I have a little problem. I have to delete all the data from our database. I don't want to drop tables, only delete the data sets.

These are 47 tables in total.

I tried the following:

Delete FROM(select TABLE_NAME from USER_TABLES);
Delete FROM(select * from ALL_ALL_TABLES Where OWNER = 'DB_NAME')

But that is not valid. How can I efficiently remove all the data from all my tables?

3 Answers 3


Generate the statements you need

select 'DELETE FROM '||table_name||' ;' from user_tables;

Even better would be using TRUNCATE instead of DELETE. That would prevent generating extra redo vectors in the redo log - in the end all you want to do is just get rid of the data. It would also avoid all actions by any DELETE triggers that the tables have.

select 'TRUNCATE TABLE '||table_name||' ;' from user_tables;
  • 4
    Or better TRUNCATE instead of DELETE FROM – there's really no need to generate extra redo vectors in the redo log if all you want to do is just get rid of the data.
    – YasirA
    Aug 21, 2014 at 8:57

You should use TRUNCATE instead of DELETE if you is using SQL Server. Because it will reduce log records in log file.

USE <your database>;

DECLARE @table_name varchar(100);

DECLARE c CURSOR FOR SELECT name FROM sys.tables WHERE type='U' 
FETCH NEXT FROM c INTO @table_name
    EXEC (N'TRUNCATE TABLE dbo.'+@table_name);
    FETCH NEXT FROM c INTO @table_name
  • 2
    The question obviously has "oracle" tag.
    – YasirA
    Aug 21, 2014 at 10:27

TRUNCATE TABLE_NAME; does the magic for you.

  • 1
    Question was to delete from all tables without having to write out each of the 47 TRUNCATE commands Aug 21, 2014 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.