；任務名稱：生產環境下schema ELON數據遷移至schema TIAN

########################################

測試一：測試參數

數據泵數據導出：
expdp system/oracle SCHEMAS=ELON directory=EXPDP_DIR dumpfile =ELON.dmp logfile=ELON_exp.log
確認兩個用戶有沒有重名的對象：
SQL> select * from dba_objects where object_name in (select object_name from dba_objects where owner='ELON') and owner ='ELON_TEST';
no rows selected
數據泵數據導入：
impdp system/oracle directory=EXPDP_DIR dumpfile=ELON.dmp logfile=ELON_TEST_impdp.log remap_schema=ELON:ELON_TEST

導入過程中的報錯信息：
ORA-39083: Object type REF_CONSTRAINT:"ELON_TEST"."FK_INFO_ID" failed to create with error:
ORA-02298: cannot validate (ELON_TEST.FK_INFO_ID) - parent keys not found
Failing sql is:
ALTER TABLE "ELON_TEST"."T_INFO" ADD CONSTRAINT "FK_INFO_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT:"ELON_TEST"."FK_N_ID" failed to create with error:
ORA-02298: cannot validate (ELON_TEST.FK_N_ID) - parent keys not found
Failing sql is:
ALTER TABLE "ELON_TEST"."T_BANKCARD" ADD CONSTRAINT "FK_N_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT:"ELON_TEST"."FK_BO_ID" failed to create with error:
ORA-02298: cannot validate (ELON_TEST.FK_BO_ID) - parent keys not found
Failing sql is:
ALTER TABLE "ELON_TEST"."BOR" ADD CONSTRAINT "FK_BO_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE
經過排查，3張子表與比父表都多了一行數據，造成創建外鍵約束失敗
SQL> select * from "ELON_TEST"."T_INFO" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
125509 128043 2018032938791546 D 0
SQL> delete from "ELON_TEST"."T_INFO" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
1 row deleted.
SQL> ALTER TABLE "ELON_TEST"."T_INFO" ADD CONSTRAINT "FK_INFO_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE;
Table altered.
SQL> select * from "ELON_TEST"."T_BANKCARD" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
89890 128043 scott
SQL> delete from "ELON_TEST"."T_BANKCARD" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
SQL> ALTER TABLE "ELON_TEST"."T_BANKCARD" ADD CONSTRAINT "FK_N_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE

SQL> select * from "ELON_TEST"."BOR" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
89890 128043 40000 0 1 0 0 0 system_FK016 09-4月 -18
SQL> delete from "ELON_TEST"."BOR" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
SQL> ALTER TABLE "ELON_TEST"."BOR" ADD CONSTRAINT "FK_BO_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE

總結：
1：生產環境，動態數據寫入，子表行數多與父表，在導入外鍵約束時，失敗
2：在生產環境，有新數據寫入的情況下，進行一致性邏輯導出，可以使用：參數fiashback_time或flashback_scn 可以實現數據一致性。flashback_time=systimestamp

方法二， 生成當前的scn，該生成動作會促使scn+1 SYS >select current_scn from v$database;? CURRENT_SCN ----------- 11608775792?

?

expdp system/xxxxxx SCHEMAS=SCOTT directory=DIR_RMAN_BACKUP dumpfile=SCOTT.dmp logfile=SCOTT_exp.log flashback_scn=11608775792?

如果報ORA-39150，就用下面的選項：flashback_time=to_timestamp(localtimestamp)? ? ??flashback_time=to_timestamp_tz(systimestamp)

?

?

轉載于:https://www.cnblogs.com/elontian/p/8819666.html

總結

以上是生活随笔為你收集整理的生产环境下，oracle不同用户间的数据迁移。第一部分的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。