How to truncate all user tables?
46,231
Solution 1
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;
/
Solution 2
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;
Solution 3
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;
/
Solution 4
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
Comments
-
szaman over 3 years
How can I truncate all user table in oracle? I have problem with tables constraints.
-
mmoossen about 9 yearssadly 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.
-
Lyuboslav Karmidzhanov over 5 years@APC, I thought when a table is truncated, it doesn't affect any of the table's indexes, triggers, or dependencies?
-
APC over 5 years@LyuboslavKarmidzhanov - I said "constraints": we can't truncate a table if there are dependent tables with foreign keys referencing it.
-
APC over 5 yearsNote that this won't work if there are dependent tables in other schemas which reference tables in the user's schema.
-
APC over 5 yearsNote that this won't work if there are dependent tables in other schemas which reference tables in the user's schema.