impdp ORA-39002,ORA-39166,ORA-39164的問題及解決

今天在做imp和impdp的效能測試時，發現如果表中存在lob欄位，載入真是慢的厲害，每秒鐘大概1000條的樣子，按照這種速度，基本上不用幹活了。
比如5千萬條記錄，50000000/1000/60/60=13.89小時，時間是無法接受的。
所以嘗試使用impdp來看看效能的提升。
匯出的表裡面有9千萬條記錄，而且做了分割槽，分割槽大概有300個。如果使用全表匯出匯入，在之前的測試中，測試5千萬資料，大概會有3個多小時，也算是比較長的時間，而且隨著資料量的增大，時間還會不斷的增長。
個人嘗試從分割槽的角度做些工作。
匯出分割槽，然後按照分割槽匯入。
使用的impdp命令如下，已經做了remap_schema,但是不管怎麼嘗試，都會丟擲如下的錯誤。事實上這個分割槽是存在的。
impdp mig_test/mig_test directory=memo_dir dumpfile=par1_mo1_memo.dmp logfile=par1_mo1_memo_imp.log  tables=mig_test.mo1_memo:P9_A0_E5 TABLE_EXISTS_ACTION=append REMAP_SCHEMA=prdappo:MIG_TEST DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS


With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object MIG_TEST.MO1_MEMO was not found.
ORA-39164: Partition MIG_TEST.MO1_MEMO:P9_A0_E5 was not found. 

嘗試了各種方法。還是沒有效果。最後檢視metalink找到了一些思路。(Doc ID 550200.1)

CAUSE

Unlike fromuser/touser and tables functionality in traditional imp, DataPump assumes that if TABLES parameter does not include schema name then the table is owned by current user doing import and will not find correct table to import unless the user doing import is same user which owns the tables in export dump and has IMP_FULL_DATABASE role so that user can import into other schemas. 

SOLUTION

1. Either grant IMP_FULL_DATABASE to user which owns the objects in the export dump so that user can import into other schema referenced REMAP_SCHEMA and run DataPump import as that schema, ie


Or:

2. Be sure to include the schema name in TABLES parameter so the correct table can be found to import from user/to user referenced in REMAP_SCHEMA, ie


最後嘗試使用如下的命令，終於有反應了，分割槽裡竟然還是空的。：）

impdp mig_test/mig_test directory=memo_dir dumpfile=par1_mo1_memo.dmp logfile=par1_mo1_memo_imp.log  tables=prdappo.mo1_memo:P9_A0_E5 remap_schema=prdappo:mig_test TABLE_EXISTS_ACTION=append  DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
Master table "MIG_TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MIG_TEST"."SYS_IMPORT_TABLE_01":  mig_test/******** directory=memo_dir dumpfile=par1_mo1_memo.dmp logfile=par1_mo1_memo_imp.log tables=prdappo.mo1_memo:P9_A0_E5 remap_schema=prdappo:mig_test TABLE_EXISTS_ACTION=append DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS 
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "MIG_TEST"."MO1_MEMO" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MIG_TEST"."MO1_MEMO":"P9_A0_E5"                0 KB       0 rows
Job "MIG_TEST"."SYS_IMPORT_TABLE_01" successfully completed at 17:23:04