使用oracle数据泵时报ORA-39097
使用oracle数据泵时报ORA-39097
测试环境在使用impdp时报错，报错信息如下：
Import: Release10.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 Database10gEnterprise Edition Release10.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
该报错主要是因为在10g或11g版本的RAC环境中，将优化器模式设置为RULE造成的。由于RBO在10G中已不被支持，所以该报错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 on10-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 inBug 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 https://www.360docs.net/doc/737473038.html,ER_DATAPUMP_JOBS
WHERE (JOB_NAME = '
when OPTIMIZER_MODE=RULE.
Since RBO is not supported in10g( seeNote 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