SQL: delete all the data from all available tables

Question

I am using oracle DB to maintain more than 30 tables, how can I delete all the data from all the tables? I only want to delete the data but not drop the tables.

Answer 1

There is no command 'ALTER TABLE XXX DISABLE ALL CONSTRAINTS'

I propose this;

BEGIN   FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')   LOOP     EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' disable constraint ' || c.constraint_name);   END LOOP;   FOR c IN (SELECT table_name FROM user_tables)   LOOP     EXECUTE IMMEDIATE ('truncate table ' || c.table_name);   END LOOP;   FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')   LOOP     EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' enable constraint ' || c.constraint_name);   END LOOP; END; 

Answer 2

Generate a script to truncate (= remove all rows from) all tables:

select 'truncate table ' || table_name || ';' from user_tables 

And then execute the script.