Oracle导入数据其实很简单,但是如果数据存在约束:如主外键、主键约束、唯一约束,可能给数据导入带来很大的麻烦。比如主外键,如果批量导入数据,是难于指定导入的先后顺序的,相信有不少入门级朋友们,会遇到跟我一样的问题。因此,为了节省大家的宝贵时间,特此分享自己的研究成果。 个人解决方案为:在导入过程中,先导入表结构,再禁用约束,其次导入数据,最终启用约束即可。其核心就是禁用和启用约束过程的创建。具体步骤如下: (1)、导出(分为2步):导出结构、导出数据 --只导出表结构 $exp dev_sem/dev_sem@pcmdb file=d:\struct.dmp log=d:\struct.log rows=n; --导出数据 $exp dev_sem/dev_sem@pcmdb file=d:\data.dmp log=d:\data.log ;
(2)、导入结构 --导入表结构 $imp file=d:\struct.dmp log=d:\imp.log full=y; (3)、编写过程 CREATE OR REPLACE PROCEDURE MANAGE_USER_CONSTRAINTS(OPERATION VARCHAR2, FK BOOLEAN DEFAULT TRUE, PK BOOLEAN DEFAULT TRUE, UK BOOLEAN DEFAULT TRUE) IS ST VARCHAR2(255); CURSOR R IS SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R';
CURSOR P IS SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P';
CURSOR U IS SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'U';
BEGIN IF UPPER(OPERATION) IN ('DROP', 'DISABLE') THEN IF FK THEN BEGIN FOR E IN R LOOP ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '|| E.CONSTRAINT_NAME; EXECUTE IMMEDIATE (ST); DBMS_OUTPUT.PUT_LINE(ST); END LOOP; END; END IF; IF PK THEN BEGIN FOR E IN R LOOP ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '|| E.CONSTRAINT_NAME; EXECUTE IMMEDIATE (ST); DBMS_OUTPUT.PUT_LINE(ST); END LOOP; END; BEGIN FOR E IN P LOOP ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '|| E.CONSTRAINT_NAME; EXECUTE IMMEDIATE (ST); DBMS_OUTPUT.PUT_LINE(ST); END LOOP; END; END IF; IF UK THEN BEGIN FOR E IN U LOOP ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '|| E.CONSTRAINT_NAME; EXECUTE IMMEDIATE (ST); DBMS_OUTPUT.PUT_LINE(ST); END LOOP; END; END IF; ELSIF UPPER(OPERATION) IN ('ENABLE') THEN IF PK THEN BEGIN FOR E IN P LOOP ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '|| E.CONSTRAINT_NAME; EXECUTE IMMEDIATE (ST); DBMS_OUTPUT.PUT_LINE(ST); END LOOP; END; END IF; IF FK THEN BEGIN FOR E IN P LOOP ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '|| E.CONSTRAINT_NAME; EXECUTE IMMEDIATE (ST); DBMS_OUTPUT.PUT_LINE(ST); END LOOP; END; BEGIN FOR E IN R LOOP ST :='ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '|| E.CONSTRAINT_NAME; EXECUTE IMMEDIATE (ST); DBMS_OUTPUT.PUT_LINE(ST); END LOOP; END; END IF; IF UK THEN BEGIN FOR E IN U LOOP ST :='ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '|| E.CONSTRAINT_NAME; EXECUTE IMMEDIATE (ST); DBMS_OUTPUT.PUT_LINE(ST); END LOOP; END; END IF; ELSE DBMS_OUTPUT.PUT_LINE('the first parameter of the procedure must be DROP or ENABLE or DISABLE'); END IF; END; / --调用过程:禁用约束检查 exec MANAGE_USER_CONSTRAINTS('disable',true,true,true); (4)、导入数据 $imp file=d:\data.dmp log=d:\data.log ignore=y full=y; (5)、启用约束 exec MANAGE_USER_CONSTRAINTS('enable',true,true,true); (6)、删除过程 drop procedure MANAGE_USER_CONSTRAINTS;
如上查找即正确无误的导入数据,而不用担心导入的先后顺序问题了。
|