通過impdp做資料庫遷移遇到的問題總結

初始的時候通過兩個庫之間直接轉儲資料，命令如下：

在要匯入的庫的OS下執行：

[oracle@qqdb auto_dmp]$ impdp tests/tests tables=ABC directory=dump_dir logfile=logfile.log network_link=crm_test table_exists_action=APPEND

[@more@]Import: Release 10.2.0.1.0 - 64bit Production on Saturday, 30 October, 2010 2:28:40

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TESTS"."SYS_IMPORT_TABLE_01": tests/******** tables=ABC directory=dump_dir logfile=logfile.log network_link=crm_test table_exists_action=APPEND
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'CRMREPORT' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."ABC" ("A" CLOB, "B" BLOB, "C" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "CRMREPORT" LOB ("A") STORE AS ( TABLESPACE "CRMREPORT" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCA
Job "TESTS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 02:28:56
[oracle@qqdb auto_dmp]$

因為我已經在目標庫內建立了對應的ABC表，而且我匯入的時候TABLE_EXISTS_ACTION設定的為APPEND，按理說應該直接將資料append進去的，可是結果卻是還要重新建立表，但是此時有LOB欄位的存在，所以加入如下引數：

REMAP_TABLESPACE=CRMREPORT:USERS

此時重新執行：

[oracle@qqdb auto_dmp]$ impdp tests/tests tables=ABC directory=dump_dir logfile=logfile.log network_link=crm_test table_exists_action=APPEND REMAP_TABLESPACE=CRMREPORT:USERS

Import: Release 10.2.0.1.0 - 64bit Production on Saturday, 30 October, 2010 2:30:57

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TESTS"."SYS_IMPORT_TABLE_01": tests/******** tables=ABC directory=dump_dir logfile=logfile.log network_link=crm_test table_exists_action=APPEND REMAP_TABLESPACE=CRMREPORT:USERS
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SCOTT"."ABC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
. . imported "SCOTT"."ABC" 1 rowsJob "TESTS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 02:32:12

此時可以看到已經轉入了資料。

因為線上impdp的時候通過database link匯入的時候，只能對應的 匯入和database link對應的使用者裡面，如果想要匯入其他的使用者可以指定如下引數：

REMAP_SCHEMA=SCOTT:TESTS

執行結果如下：

[oracle@qqdb auto_dmp]$ impdp tests/tests tables=ABC directory=dump_dir logfile=logfile.log network_link=crm_test table_exists_action=APPEND REMAP_TABLESPACE=CRMREPORT:USERS REMAP_SCHEMA=SCOTT:TESTS

Import: Release 10.2.0.1.0 - 64bit Production on Saturday, 30 October, 2010 2:33:26

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TESTS"."SYS_IMPORT_TABLE_01": tests/******** tables=ABC directory=dump_dir logfile=logfile.log network_link=crm_test table_exists_action=APPEND REMAP_TABLESPACE=CRMREPORT:USERS REMAP_SCHEMA=SCOTT:TESTS
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TESTS"."ABC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
. . imported "TESTS"."ABC" 1 rowsJob "TESTS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 02:34:41

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