3

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?

7

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. – Yasir Arsanukaev Aug 21 '14 at 8:57
-6

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>;
GO

DECLARE @table_name varchar(100);

DECLARE c CURSOR FOR SELECT name FROM sys.tables WHERE type='U' 
OPEN c
FETCH NEXT FROM c INTO @table_name
WHILE @@FETCH_STATUS =0
BEGIN
    EXEC (N'TRUNCATE TABLE dbo.'+@table_name);
    FETCH NEXT FROM c INTO @table_name
END
CLOSE c
DEALLOCATE c
  • 2
    The question obviously has "oracle" tag. – Yasir Arsanukaev Aug 21 '14 at 10:27
-6

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 – Mark Sinkinson Aug 21 '14 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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