Bir tabloda CONSTRAINT tanımlı ise o tabloyu truncate edemezsiniz. Önce CONSTRAINT'leri DISABLE etmeniz gerekiyor daha sonra TRUNCATE ve ENABLE yapmanız gerek.
Alttaki script tüm bu işlemleri yapan scripti hazırlıyor. dbms_output.put_line ile çıkan scrip daha sonra manuel çalıştırmanız gerekmektedir.
declare
v_str varchar2(1000);
v_owner varchar2(50);
v_table varchar2(50);
begin
v_owner := 'DT_SCHEMA_DBA';
v_table := 'MUSTERI';
for c1 in (select owner,table_name from all_tables where owner=v_owner and table_name = v_table)
loop
begin
for c2 in (select a.owner,a.table_name,a.constraint_name
from all_constraints a, all_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
and a.r_owner = c1.owner
and b.table_name = c1.table_name)
loop
v_str:='alter table '||c2.owner||'.'||c2.table_name||
' disable constraint '
||c2.constraint_name;
dbms_output.put_line(v_str||';');
--execute immediate v_str;
end loop;
--execute immediate 'truncate table '||c1.owner||'.'||c1.table_name;
--dt_schema_dba.truncate_table_sp(v_owner,v_table);
v_str := 'truncate table '||v_owner||'.'||v_table;
dbms_output.put_line(v_str||';');
for c2 in (select a.owner,a.table_name,a.constraint_name
from all_constraints a, all_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
and a.r_owner = c1.owner
and b.table_name = c1.table_name)
loop
v_str:='alter table '||c2.owner||'.'||c2.table_name||
' enable constraint '
||c2.constraint_name;
dbms_output.put_line(v_str||';');
--execute immediate v_str;
end loop;
exception
when others then
dbms_output.put_line(c1.table_name||' '||sqlerrm);
end;
end loop;
end;
Subscribe