EXPDP出來的DMP文件包含2個Schema的表，現在要IMPDP到一個Schema裡面試了幾把都報錯，好久不用邏輯導出入，折騰了好久，出現各種錯誤1、創建目錄並授權create or replace directory DUMPDIR as '/home/oracle/dump_dir';gra ...
EXPDP出來的DMP文件包含2個Schema的表，現在要IMPDP到一個Schema裡面
試了幾把都報錯，好久不用邏輯導出入，折騰了好久，出現各種錯誤
1、創建目錄並授權
create or replace directory DUMPDIR as '/home/oracle/dump_dir';
grant read,write on directory DUMPDIR to test2;
2、ORDER用戶的表T_ORDER_DEPOSIT_530 impdp導入TEST2用戶下
impdp test2/test123 remap_schema=ORDER:TEST2 tables=ORDER.T_ORDER_DEPOSIT_530 directory=DUMPDIR dumpfile=OA188672.dmp logfile=OA188672.log
----------------------------------------------------------------------------------------
err log：
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** parallel=4 remap_schema=TESTUSER:TEST dumpfile=OA186751.dmp logfile=OA186751_imp.log DIRECTORY=DUMPDIR
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-31625: Schema MEMBERS is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** remap_table=TESTUSER.T_ORDER_518:TEST.T_ORDER_518 dumpfile=OA186751.dmp logfile=OA186751_imp_t_order.log DIRECTORY=DUMPDIR
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-31625: Schema MEMBERS is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** TABLES=TESTUSER.T_ORDER_518,TESTUSER.T_ORDER_SETTLE_INFO_518,TESTUSER.T_MERCHANT_SETTLE_518,TESTUSER.T_ORDER_ABNORMAL_518,TESTUSER.T_ORDER_REFUND_518,MEMBERS.T_RECIEVE_PAY_TRADE_6MER0504 dumpfile=OA186751.dmp logfile=OA186751_imp.log DIRECTORY=DUMPDIR
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"MEMBERS"."T_RECIEVE_PAY_TRADE_6MER0504" failed to create with error:
ORA-01918: user 'MEMBERS' does not exist
Failing sql is:
上面涉及兩點，1、Schema變了-> remap_schema來轉換,2、TABLE要指定Schema
下麵將MEMBERS用戶下備份的表恢復到TEST2用戶下
[oracle@localhost dump_dir]$ impdp test2/test123 remap_schema=MEMBERS:TEST2 tables=MEMBERS.T_RECIEVE_PAY_TRADE_6MER0504 directory=DUMPDIR dumpfile=OA186751.dmp logfile=OA186751_MEMBERS.log
Import: Release 11.2.0.1.0 - Production on Mon May 21 19:33:24 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST2"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST2"."SYS_IMPORT_TABLE_01": test2/******** remap_schema=MEMBERS:TEST2 tables=MEMBERS.T_RECIEVE_PAY_TRADE_6MER0504 directory=DUMPDIR dumpfile=OA186751.dmp logfile=OA186751_MEMBERS.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST2"."T_RECIEVE_PAY_TRADE_6MER0504" 82.31 KB 567 rows
Job "TEST2"."SYS_IMPORT_TABLE_01" successfully completed at 19:33:32
再對另外一個Schema進行IMPDP就可以了。。。
錯誤例子：
下麵這個首先關鍵字就沒選好，只是想把表從一個用戶導入到另外一個用戶，而remap_table是修改表名稱
--impdp test/test123 DIRECTORY=DUMPDIR remap_table=MEMBER.T_RECIEVE_PAY_TRADE_6MER0504:TEST.T_RECIEVE_PAY_TRADE_6MER0504 dumpfile=OA186751.dmp logfile=OA186751_imp.log
參考及示例出處：
https://blog.csdn.net/e_wsq/article/details/78374020
remap_table、remap_tablespace多組對象轉換的話，每組對象之間用逗號隔開
tables多張表時，每張表之間用逗號隔開
Remap_tablespace如果需要轉換多個表空間，如A1轉換成B1，A2轉換成B1，有如下兩種方式
remap_tablespace=A1:B1 remap_tablespace=A2:B1
remap_tablespace= A1:B1, A2:B1
Remap_table如果需要轉換多個表名
同一個schema的情況下，如A1轉換成B1，A2轉換成B1，有如下兩種方式
remap_table=A1:B1 remap_table=A2:B1
remap_table= A1:B1, A2:B1
remap_table、remap_tablespace多組對象轉換的話，每組對象之間用逗號隔開
tables多張表時，每張表之間用逗號隔開
Remap_tablespace如果需要轉換多個表空間，如A1轉換成B1，A2轉換成B1，有如下兩種方式
remap_tablespace=A1:B1 remap_tablespace=A2:B1
remap_tablespace= A1:B1, A2:B1
Remap_table如果需要轉換多個表名
同一個schema的情況下，如A1轉換成B1，A2轉換成B1，有如下兩種方式
remap_table=A1:B1 remap_table=A2:B1
remap_table= A1:B1, A2:B1
跨schema只導出導入表並重命名的標準方式如下
expdp時tables寫上schema.表名並用逗號隔開
impdp時需要remap_schema， tables可寫可不寫，remap_table冒號前面的表名加上schema，冒號後面的表名不加schema
expdp system/123456 tables=t01.test101,t01.test102,t01.test103 directory=DATA_PUMP_DIR dumpfile=t011.dbf
一個案例
A伺服器導出用戶ESB的一張分區表ESB_LOG
expdp system/123456 directory=DATA_PUMP_DIR dumpfile=ESB_LOG.dmp logfile=ESB_LOG.log tables=ESB.ESB_LOG
導入到B伺服器的part用戶，表名改為ESB_LOG2(加了tables=ESB.ESB_LOG，其實加不加無所謂，反正ESB_LOG.dm就是只有一張表ESB.ESB_LOG)
impdp system/123456 directory=DATA_PUMP_DIR dumpfile=ESB_LOG.dmp logfile=ESB_LOG2.log tables=ESB.ESB_LOGremap_schema=ESB:part remap_tablespace=ESB07:part,ESB08:part,ESB09:part,ESB10:part,ESB11:part,ESB12:part,ESB:part remap_table=ESB.ESB_LOG:ESB_LOG2
