【备份语句】
导出语句:exp ACTIVITI/ACTIVITI@OA owner=OA file=ACTIVITI2020.DMP log=log.txt
导入语句:imp ACTIVITI/ACTIVITI@OA file=ACTIVITI2020.dmp log=log.txt full=y ignore=y
废话不多说,直接上错误日志;
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 经由常规路径由 EXPORT:V11.02.00 创建的导出文件 已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入 . 正在将 ACTIVITI 的对象导入到 ACTIVITI . . 正在导入表 "ACT_EVT_LOG"导入了 0 行 . . 正在导入表 "ACT_GE_BYTEARRAY" IMP-00019: 由于 ORACLE 错误 2291 而拒绝行 IMP-00003: 遇到 ORACLE 错误 2291 ORA-02291: 违反完整约束条件 (ACTIVITI.ACT_FK_BYTEARR_DEPL) - 未找到父项关键字 列 1 106 列 2 1 列 3 bpmn/GroupCivilizationCommittee.png 列 4 105 列 5 00780001010C080000010000000100001246D573000376DA00... 列 6 0 IMP-00019: 由于 ORACLE 错误 2291 而拒绝行 IMP-00003: 遇到 ORACLE 错误 2291 ORA-02291: 违反完整约束条件 (ACTIVITI.ACT_FK_BYTEARR_DEPL) - 未找到父项关键字 列 1 107 列 2 1 列 3 bpmn/GroupCivilizationCommittee.bpmn20.xml 列 4 105 列 5 006C0001010C080000010000000100001246D574000376DA00... 列 6 0 IMP-00019: 由于 ORACLE 错误 2291 而拒绝行 IMP-00003: 遇到 ORACLE 错误 2291 ORA-02291: 违反完整约束条件 (ACTIVITI.ACT_FK_BYTEARR_DEPL) - 未找到父项关键字 列 1 110 列 2 1 列 3 bpmn/GroupOrgPartyCommittee.bpmn20.xml 列 4 109 列 5 006C0001010C080000010000000100001246D575000376DA00... 列 6 0 IMP-00019: 由于 ORACLE 错误 2291 而拒绝行 IMP-00003: 遇到 ORACLE 错误 2291 ORA-02291: 违反完整约束条件 (ACTIVITI.ACT_FK_BYTEARR_DEPL) - 未找到父项关键字 列 1 111 列 2 1 列 3 bpmn/GroupOrgPartyCommittee.png 列 4 109 列 5 00780001010C080000010000000100001246D576000376DA00... 列 6 0
导入完成后,发现表里数据不完整,纳尼头大了;看日志,发现是由于导入的时候,表直接存在主外键约束问题,引起的错误;
解决思路:先禁用约束,导完后再启用。
1.编写过程,禁用约束
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;
2.过程执行
第一个参数填disable,其它空,然后执行,成功后就愉快的执行导入语句了,最后别忘了启用;参数说明:disable=禁用,enable=启用