SQL: delete all the data from all available tables

oraclesql

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.

Best Answer

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;