create or replace procedure truncate_all_table
as
l_str varchar2(1000);
begin
for x in (select tname from tab where tabtype='TABLE') loop
l_str:='truncate table '||x.tname;
execute immediate l_str;
end loop;
end;
/
create or replace procedure disable_all_constraints
as
l_str varchar2(1000);
begin
for x in (select constraint_name,table_name from user_cons_columns) loop
l_str:='alter table ' || x.table_name || ' disable constraint ' || x.constraint_name || ' cascade';
execute immediate l_str;
end loop;
end;
/
create or replace procedure enable_all_constraints
as
l_str varchar2(1000);
begin
for x in (select constraint_name,table_name from user_cons_columns) loop
l_str:='alter table ' || x.table_name || ' enable constraint ' || x.constraint_name;
execute immediate l_str;
end loop;
end;
/