How to truncate all user tables?

Question

How can I truncate all user table in oracle? I have problem with tables constraints.

score 10 · Answer 1 · answered Jun 30 '11 at 09:56

declare

begin

for c1 in (select table_name, constraint_name from user_constraints) loop
    begin
        execute immediate ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
    end;
end loop;

for t1 in (select table_name from user_tables) loop
    begin
        execute immediate ('truncate table '||t1.table_name);
    end;
end loop;

for c2 in (select table_name, constraint_name from user_constraints) loop
    begin
        execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
    end;
end loop;

end;
/

Note that this won't work if there are dependent tables **in other schemas** which reference tables in the user's schema. — APC, Jan 09 '19 at 09:53

score 8 · Answer 2 · answered Aug 22 '16 at 12:09

Improved the above script in case you can't remove the constraint because dependencies exist (in the form of foreign keys that are dependent on this constraint - ORA-02297.) and by printing all (disable, truncate and enable) statements.

set serveroutput on;

declare

begin

for c1 in (select y1.table_name, y1.constraint_name from user_constraints y1, user_tables x1 where x1.table_name = y1.table_name order by y1.r_constraint_name nulls last) loop
    begin
        dbms_output.put_line('alter table '||c1.table_name||' disable constraint '||c1.constraint_name || ';');
        execute immediate  ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
    end;
end loop;

for t1 in (select table_name from user_tables) loop
    begin
        dbms_output.put_line('truncate table '||t1.table_name || ';');    
        execute immediate ('truncate table '||t1.table_name);
    end;
end loop;

for c2 in (select y2.table_name, y2.constraint_name from user_constraints y2, user_tables x2 where x2.table_name = y2.table_name order by y2.r_constraint_name nulls first) loop
    begin
        dbms_output.put_line('alter table '||c2.table_name||' enable constraint '||c2.constraint_name || ';');        
        execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
    end;
end loop;

end;

Note that this won't work if there are dependent tables **in other schemas** which reference tables in the user's schema. — APC, Jan 09 '19 at 09:53

score 2 · Answer 3 · answered Nov 08 '09 at 15:08

2

No need for variables

begin
  for r in (select table_name from user_tables) loop
    execute immediate 'truncate table ' || r.table_name;
  end loop;
end;

Regards K

answered Nov 08 '09 at 15:08

Khb

1,423
9
9

@APC, I thought when a table is truncated, it doesn't affect any of the table's indexes, triggers, or dependencies? – Lyuboslav Karmidzhanov Jan 09 '19 at 09:48
@LyuboslavKarmidzhanov - I said *"constraints"*: we can't truncate a table if there are dependent tables with foreign keys referencing it. – APC Jan 09 '19 at 09:51

score 2 · Answer 4 · edited Jun 27 '13 at 07:10

Improvement in case you have special constraints which make above script fail:

set serveroutput on;

declare

begin

for c1 in (select y.table_name, y.constraint_name from user_constraints y, user_tables x where x.table_name = y.table_name) loop
    begin
        dbms_output.put_line('alter table '||c1.table_name||' disable constraint '||c1.constraint_name || ';');
        execute immediate  ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
    end;
end loop;

for t1 in (select table_name from user_tables) loop
    begin
        execute immediate ('truncate table '||t1.table_name);
    end;
end loop;

for c2 in (select table_name, constraint_name from user_constraints) loop
    begin
        execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
    end;
end loop;

end;
/

sadly constraints are 'constrained' by order. you can fix the deactivation of the constraints by adding 'cascade' at the end of the 'disable' command. but most likely you wont be able to automate the activation of the constraints. — mmoossen, Mar 30 '15 at 08:19

score 0 · Answer 5 · answered Dec 21 '12 at 16:48

0

You can output, then execute the one you like:

set serveroutput on;

begin

for r in (select table_name from user_tables) loop

    dbms_output.put_line('truncate table ' || r.table_name);

  end loop;

end;

answered Dec 21 '12 at 16:48

Israel Margulies

8,656
2
30
26

How to truncate all user tables?

5 Answers5

Linked