今天在做imp和impdp的性能測(cè)試時(shí)，發(fā)現(xiàn)如果表中存在lob字段，加載真是慢的厲害，每秒鐘大概1000條的樣子，按照這種速度，基本上不

今天在做imp和impdp的性能測(cè)試時(shí)，發(fā)現(xiàn)如果表中存在lob字段，，加載真是慢的厲害，每秒鐘大概1000條的樣子，按照這種速度，基本上不用干活了。

比如5千萬條記錄，50000000/1000/60/60=13.89小時(shí)，時(shí)間是無法接受的。

所以嘗試使用impdp來看看性能的提升。

導(dǎo)出的表里面有9千萬條記錄，而且做了分區(qū)，分區(qū)大概有300個(gè)。如果使用全表導(dǎo)出導(dǎo)入，在之前的測(cè)試中，測(cè)試5千萬數(shù)據(jù)，大概會(huì)有3個(gè)多小時(shí)，也算是比較長(zhǎng)的時(shí)間，而且隨著數(shù)據(jù)量的增大，時(shí)間還會(huì)不斷的增長(zhǎng)。

個(gè)人嘗試從分區(qū)的角度做些工作。

導(dǎo)出分區(qū)，然后按照分區(qū)導(dǎo)入。

使用的impdp命令如下，已經(jīng)做了remap_schema,但是不管怎么嘗試，都會(huì)拋出如下的錯(cuò)誤。事實(shí)上這個(gè)分區(qū)是存在的。

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)

通過expdp&impdp把11g的數(shù)據(jù)遷移到10g平臺(tái)的要點(diǎn)

Oracle Data Pump使用范例及部分注意事項(xiàng)(expdp/impdp)

Oracle datapump expdp/impdp 導(dǎo)入導(dǎo)出數(shù)據(jù)庫時(shí)hang住

expdp/impdp做Oracle 10g 到11g的數(shù)據(jù)遷移

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

SQL> grant IMP_FULL_DATABASE to old_user;

impdp old_user/passwd TABLES=TABLEA:TABLEA_PARTITION1 /

REMAP_SCHEMA=old_user:new_user DUMPFILE=exp01.dmp,exp02.dmp,exp03.dmp /

DIRECTORY=data_pump_dir

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 system/passwd TABLES=old_user.TABLEA:TABLEA_PARTITION1 /

REMAP_SCHEMA=old_user:new_user DUMPFILE=exp01.dmp,exp02.dmp,exp03.dmp /

DIRECTORY=data_pump_dir

最后嘗試使用如下的命令，終于有反應(yīng)了，分區(qū)里竟然還是空的。：)

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

本文永久更新鏈接地址：

本文原創(chuàng)發(fā)布php中文網(wǎng)，轉(zhuǎn)載請(qǐng)注明出處，感謝您的尊重！

總結(jié)

以上是生活随笔為你收集整理的oracle导入时 ora39166,impdp ORA-39002,ORA-39166,ORA-39164的问题及解决的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。