資料庫使用truncate清理很多表時碰到外鍵約束時怎麼快速解決

問題處理思路：

1. 先將資料庫中涉及到外鍵約束的表置為無效狀態

2.待清除完所有表資料後再將外鍵約束的表置為可用狀態

具體實現指令碼：

declare

begin

for vv_sql in (SELECT 'ALTER TABLE '|| table_name ||' DISABLE CONSTRAINT ' || CONSTRAINT_NAME alter_sql FROM USER_CONSTRAINTS

where CONSTRAINT_TYPE = 'R')

loop

execute immediate vv_sql.alter_sql;

end loop;

execute immediate 'truncate table 表名;

................. ---此處省略，可用寫多個executesql語句

for vv_sql in (SELECT 'ALTER TABLE '|| table_name ||' ENABLE CONSTRAINT ' || CONSTRAINT_NAME alter_sql FROM USER_CONSTRAINTS

where CONSTRAINT_TYPE = 'R')

loop

execute immediate vv_sql.alter_sql;

end loop;

end;

/