遷移後處理外來鍵約束的問題

資料導導新的測試庫後，因為在impdp的時候沒有表的順序，這裡會造成新測試庫上面的所有表都沒有了外來鍵約束，或者是約束有錯誤

 

 

update globalidentity set identityvalue=identityvalue+100000;

--必須在當前使用者執行

select USERENV ('SCHEMAID') from dual;--66

--dba使用者執行

update sys.seq$ set highwater=highwater+10000 where obj# in

 (select obj# from sys.obj$ where owner#=66)

 

 

 

1 約束問題

 下面是要去掉自己表列裡面有外來鍵約束卻沒有被約束到的資料，直接刪除，用拼湊字串的方式生成所需要的SQL來刪除

 select 'delete ' || c.table_name|| ' where ' || d.column_name || '>(select max('||

b.column_name || ') from ' || a.table_name|| ');'

 

from user_constraints a,user_cons_columns b,user_constraints C,user_cons_columns d

 

where a.constraint_name=b.constraint_name

and c.R_CONSTRAINT_NAME=a.constraint_name

and c.constraint_name=d.constraint_name

and a.constraint_type='P'

 

 

2 檢視要刪除的表當了多少表的‘部門’

select   a.owner 主鍵擁有者,

         a.table_name 主鍵表,

         b.column_name 主鍵列,

         C.OWNER 外來鍵擁有者,

         c.table_name 外來鍵表,

         d.column_name 外來鍵列

from user_constraints a,

     user_cons_columns b,

     user_constraints C,

     user_cons_columns d

where  a.constraint_name=b.constraint_name

and C.R_CONSTRAINT_NAME=a.constraint_name

and c.constraint_name=d.constraint_name

and a.constraint_type='P'

and a.table_name='T_TO_ORDER_INFO' --需要檢視主外來鍵關係的表

order by a.table_name

 

 

到時候級聯刪除的時候，下面只要有外來鍵約束到了這張表上面的，那麼刪除的時候下面的表對應的都會被刪除

 

3 將表的約束改為直接級聯刪除的

 

先刪除原來的外來鍵約束(刪除前要把下面的SQL先執行出來)

select  'alter table '||a.table_name||' drop constraint '||c.constraint_name||';'

 

from

user_constraints a,

user_constraints b,

user_cons_columns c,

user_cons_columns d

where

    a.r_constraint_name=b.constraint_name

and a.constraint_type='R'

and b.constraint_type='P'

and a.r_owner=b.owner

and a.constraint_name=c.constraint_name

and b.constraint_name=d.constraint_name

and a.owner=c.owner

and a.table_name=c.table_name

and b.owner=d.owner

and b.table_name=d.table_name

 

============================================================================

 

再重新新增約束，新增的時候加上級聯刪除（執行的時候要先出它的SQL，再出上面的SQL）

select  'alter table '||a.table_name||' add constraint '|| c.constraint_name||

' foreign key('||c.column_name||') references '||b.table_name||'('||d.column_name||') on delete cascade;'

 from

user_constraints a,

user_constraints b,

user_cons_columns c,

user_cons_columns d

where

    a.r_constraint_name=b.constraint_name

and a.constraint_type='R'

and b.constraint_type='P'

and a.r_owner=b.owner

and a.constraint_name=c.constraint_name

and b.constraint_name=d.constraint_name

and a.owner=c.owner

and a.table_name=c.table_name

and b.owner=d.owner

and b.table_name=d.table_name

 

上面會生成2排的SQL，先執行刪除約束的SQL，再建立級聯刪除的SQL，這樣，後面就可以進行級聯刪除了

 

 

4 分段刪除

因為刪除的表太大了，只能改為1000行1000行的刪

 

delete T_TO_ORDER_INFO WHERE ORDER_ID min(order_id)+1000 from T_TO_ORDER_INFO);

COMMIT;

 delete T_TO_ORDER_INFO WHERE ORDER_ID min(order_id)+1000 from T_TO_ORDER_INFO);

COMMIT;

這麼執行一條語句，那麼由步驟2執行出來的關聯的表對應的列都被刪除了，如果步驟2出來的有15張表，那麼刪一行資料就會刪的15張所對應的每一個資料行上面

 

今天看了一下，級聯刪除1000條要用20分鐘，這麼算下來要刪掉一張表要用800小時

 

 

上面這種方法行不通，那麼只能一層一層手動刪除，將步驟2用in的方式巢狀一次，發現17張表下面還有6張表，再看看6張表下面有沒有東西，如果沒有，就直接truncate掉，然後再來truncate那17張，但是又發現有外來鍵關係的，雖然子表沒有資料了，但是仍然不能用truncate，不這種delete就快多了

 

備註：一張由資料，索引，約束，備註等東西組成，我們仍然可以通過上面步驟2的方式找出那顆樹從根到葉的所有表，然後直接drop，用指令碼建立表，用上面步驟3的方式重建約束

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10678398/viewspace-693980/，如需轉載，請註明出處，否則將追究法律責任。