Handling constraints when truncating a table

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 violated based on the order in which the tables are truncated.

DANGER! Please ensure you are in the correct schema before running what is below.

declare
  type vtab is table of varchar2(30);
  l_enabled_constraints vtab := vtab();
  l_enabled_constraint_tabs vtab := vtab();
  i number := 0;
  j number := 0;
begin
  for cur in (select table_name,constraint_name 
                from user_constraints 
                where status = 'ENABLED' 
                  and constraint_type = 'R') loop
    i := i + 1;
    l_enabled_constraints.extend(1);
    l_enabled_constraint_tabs.extend(1);
    l_enabled_constraint_tabs(i) := cur.table_name;
    l_enabled_constraints(i) := cur.constraint_name;
  end loop;
  while j < i loop
    j := j + 1;
    execute immediate 'alter table ' || l_enabled_constraint_tabs(j) || ' disable constraint ' || l_enabled_constraints(j);
  end loop;
  for cur in (select table_name from user_tables) loop
    begin
      execute immediate 'truncate table ' || cur.table_name;
    exception
      when others then
        dbms_output.put_line(cur.table_name || ' ' || sqlerrm);
    end;
  end loop;
  j := 0;
  while j < i loop
    j := j + 1;
    execute immediate 'alter table ' || l_enabled_constraint_tabs(j) || ' enable constraint ' || l_enabled_constraints(j);
  end loop;
end;
/

After the tables have been truncated, only the constraints that were disabled are re-enabled.