ORA-39083 ORA-23421 問題分析與解決

在使用IMPDP匯入部分使用者的資料時,在匯入工作的最後階段遇到了ORA-39083錯誤,錯誤描述如下:

一. 問題描述
......

Processing object type DATABASE_EXPORT/SCHEMA/REFRESH_GROUP
ORA-39083: Object type REFRESH_GROUP failed to create with error:
ORA-23421: job number 122 is not a job in the job queue
Failing sql is:
BEGIN dbms_refresh.make('"SCDC"."PROD_SEC_INIT_DEV_MV"',list=>null,next_date=>null,interval=>null,
implicit_destroy=>TRUE,lax=>FALSE,job=>122,rollback_seg=>NULL,push_deferred_rpc=>TRUE,
refresh_after_errors=>FALSE,purge_option => 1,parallelism => 0,heap_size => 0);
dbms_refresh.add(name=>'"SCDC"."PROD_SEC_INIT_DEV_MV"',list=>'"SCDC"."PROD_SEC_INIT_DEV_MV"',siteid=>0,expo
Job "SYSTEM"."SYS_IMPORT_SCHEMA_04" completed with 39 error(s) at 16:38:15

[@more@]

二 . 問題分析

在metalink上找到一遍文章,對解決這個問題有很大幫助.

ORA-39083 ORA-23421 Impdp Fails Importing REFRESH_GROUP [ID 972344.1]

Cause
The cause of this problem has been identified in Bug 7658844 Import data pump fails importing REFRESH_GROUP with ORA-39083, ORA-23421 if a user other than the refresh group owner does the import.

Solution
To fix the issue:

1. If there is one-off Patch 7658844 available on your current release and for your platform

Or:

2. Upgrade onto release 11.2

Or:

3. Use one of the following workarounds:

- execute impdp by SYSTEM without SCHEMAS parameter, or:
- execute expdp and impdp by the job owner (you may need to pre-create the job owner on the target database).

三. 問題解決

下載補丁 7658844,檔名為:p7658844_10205_Generic,不分作業系統.

在ORACLE環境裡打上7658844 補丁,打完補丁後,需要執行兩個指令碼,重新建立相關物件
SQL>@@?/rdbms/admin/catph.sql

SQL>@@?/rdbms/admin/catdpb.sql

然後重新進行impdp匯入就沒有上面的問題了.