ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用oracle数据泵时报ORA-39097

使用oracle数据泵时报ORA-39097

原创 Linux操作系统 作者:is.x 时间:2011-05-08 13:49:14 0 删除 编辑

测试环境在使用impdp时报错,报错信息如下:

 

Import: Release 10.2.0.2.0 - 64bit Production on Monday, 28 March, 2011 16:00:04

 

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

Master table "LINC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "LINC"."SYS_IMPORT_TABLE_01":  linc/******** directory=DUMPDIR tables=fbdb_card dumpfile=linc_exp_fb.dmp

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39097: Data Pump job encountered unexpected error -1422

ORA-39065: unexpected master process exception in DISPATCH

ORA-01422: exact fetch returns more than requested number of rows

 

ORA-39097: Data Pump job encountered unexpected error -1422

ORA-39065: unexpected master process exception in DISPATCH

ORA-01422: exact fetch returns more than requested number of rows

 

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role 'MONUSER' does not exist

Failing sql is:

GRANT SELECT ON "LINC"."FBDB_CARD" TO "MONUSER"

 

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

ORA-31684: Object type INDEX:"LINC"."FBDB_CARD_ETL_DAY" already exists

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"LINC"."FBDB_CARD_ETL_DAY" already exists

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-39097: Data Pump job encountered unexpected error -1422

ORA-39065: unexpected master process exception in DISPATCH

ORA-01422: exact fetch returns more than requested number of rows

 

 

UDI-00008: operation generated ORACLE error 39078

ORA-39078: unable to dequeue message for agent KUPC$A_1_20110328160006 from queue "KUPC$S_1_20110328160005"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPC$QUE_INT", line 558

ORA-25205: the QUEUE SYS.KUPC$S_1_20110328160005 does not exist

ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712

ORA-06512: at line 1

 

该报错主要是因为在10g11g版本的RAC环境中,将优化器模式设置为RULE造成的。由于RBO10G中已不被支持,所以该报错oracle不认为是一个bug

 

将优化器模式修改后,问题解决。

 

Alter system set optimizer_mode=’ALL_ROWS’ scope=both;

 

 

Data Pump Export Fails With ORA-39097 ORA-39065 ORA-01422 [ID 577562.1]

 


Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.8 - Release: 10.1 to 11.1
Information in this document applies to any platform.

Symptoms

Checked for relevance on 10-22-2010

In a RAC database a full Data Pump export fails with:

ORA-39097: Data Pump job encountered unexpected error -1422
ORA-39065: unexpected master process exception in DISPATCH
ORA-01422: exact fetch returns more than requested number of rows

Cause

The issue is occurring when OPTIMIZER_MODE is set to RULE.

Same issue is described in Bug 5928905 - ORA-01422 WITH EXPDP WHEN 2 RAC
INSTANCES ARE RUNNING - closed as not a bug.

It looks like RBO gets wrong results for the query:
SELECT ATTACHED_SESSIONS FROM SYS.USER_DATAPUMP_JOBS
WHERE (JOB_NAME = '' );
when OPTIMIZER_MODE=RULE.

Since RBO is not supported in 10g ( see
Note 189702.1 ) this behavior. is not considered a bug.

Solution

Change the OPTIMIZER_MODE to a supported value, for example ALL_ROWS.

References

BUG:5928905 - ORA-01422 WITH EXPDP WHEN 2 RAC INSTANCES ARE RUNNING
NOTE:189702.1 - Rule Based Optimizer is to be Desupported in Oracle10g

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20750200/viewspace-694760/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-27

  • 博文量
    73
  • 访问量
    255517