Oracle doesn’t supply a TRUNCATE TABLE TABLE_NAME CASCADE CONSTRAINTS syntax structure as it does when dropping a table.<\/p>\n
The anonymous block below will truncate all tables in the current schema and handle the constraints violations (foreign keys) that may be violated based on the order in which the tables are truncated.<\/p>\n
DANGER! Please ensure you are in the correct schema before running what is below.<\/p>\n
\r\ndeclare\r\n type vtab is table of varchar2(30);\r\n l_enabled_constraints vtab := vtab();\r\n l_enabled_constraint_tabs vtab := vtab();\r\n i number := 0;\r\n j number := 0;\r\nbegin\r\n for cur in (select table_name,constraint_name \r\n from user_constraints \r\n where status = 'ENABLED' \r\n and constraint_type = 'R') loop\r\n i := i + 1;\r\n l_enabled_constraints.extend(1);\r\n l_enabled_constraint_tabs.extend(1);\r\n l_enabled_constraint_tabs(i) := cur.table_name;\r\n l_enabled_constraints(i) := cur.constraint_name;\r\n end loop;\r\n while j < i loop\r\n j := j + 1;\r\n execute immediate 'alter table ' || l_enabled_constraint_tabs(j) || ' disable constraint ' || l_enabled_constraints(j);\r\n end loop;\r\n for cur in (select table_name from user_tables) loop\r\n begin\r\n execute immediate 'truncate table ' || cur.table_name;\r\n exception\r\n when others then\r\n dbms_output.put_line(cur.table_name || ' ' || sqlerrm);\r\n end;\r\n end loop;\r\n j := 0;\r\n while j < i loop\r\n j := j + 1;\r\n execute immediate 'alter table ' || l_enabled_constraint_tabs(j) || ' enable constraint ' || l_enabled_constraints(j);\r\n end loop;\r\nend;\r\n\/\r\n<\/pre>\nAfter the tables have been truncated, only the constraints that were disabled are re-enabled.<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle doesn’t supply a TRUNCATE TABLE TABLE_NAME CASCADE CONSTRAINTS syntax structure as it does when dropping a table. The anonymous block below will truncate all tables in the current schema and handle the constraints violations (foreign keys) that may be…<\/p>\n