迁移后处理外键约束的问题

数据导导新的测试库后，因为在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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10678398/viewspace-693980/