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.

Thank you so much.

Answer 1

Google for "Oracle truncate"...

Answer 2

Clone the schema and then drop the old tables?

Answer 3

Try this script. Not tested (but what's the worst that can happen, you lose all your data? :))

Answer 4

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

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

And then execute the script.

Answer 5

The potential drawback with a truncate is that it may fail on referential integrity constraints. So you'd want to disable foreign key constraints first, then do the truncate, then re-enable constraints. The 'plus' with cloning the schema (exp and imp) is that you could also drop and recreate the tablespace too (which you may want to do if you want to reclaim some physical disk space as a result of removing all the data).

Answer 6

To address the issue of constraints, something like this should work:

BEGIN

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' DISABLE ALL CONSTRAINTS';
    END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
    END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' ENABLE ALL CONSTRAINTS';
    END LOOP;
END;