How can I truncate all user table in oracle? I have problem with tables constraints.
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;
/
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With