oracle 23421,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导入就没有上面的问题了.