Data Pump Export Fails With ORA39014, ORA39029 and ORA31672, Alert Log Shows ORA07445 [_evaopn2+578]

今天在导出数据库时，遇到ORA-39014错误，查Metalink找到如下文章,确认可用，分享给没有账号的朋友。

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3.0
This problem can occur on any platform.

Symptoms

While attempting to perform an export using Data Pump, the following errors are encountered:

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.

The alert log shows a core dump:

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_evaopn2+578] 

When reviewing the trace file the query and stack dump seen  is similar to the following:

Current SQL statement for this session:
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('PACKAGE_BODY_T', '7')), KU$.OBJ_NUM ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'PACKAGE_BODY' ,KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_EXP_PKG_BODY_VIEW KU$ WHERE KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001)))
----- PL/SQL Call Stack -----
object line object
handle number name
3A94E9B8 1313 package body SYS.DBMS_METADATA
3A94E9B8 1583 package body SYS.DBMS_METADATA
3A94E9B8 1891 package body SYS.DBMS_METADATA
3A94E9B8 4224 package body SYS.DBMS_METADATA
35BDE86C 2006 package body SYS.KUPW$WORKER
35BDE86C 6886 package body SYS.KUPW$WORKER
35BDE86C 1260 package body SYS.KUPW$WORKER
30A41988 2 anonymous block
----- Call Stack Trace -----
evaopn2 evaopn2 evaopn2 PGOSF372__opifcr 3939 PGOSF405__qervwRo wProcedure qersoFetch qervwFetch kpofrws opifch2 opifch opipls opiodr rpidrus rpidru rpiswu2 rpidrv psddr0 psdnal pevm_BFTCHC pfrinstr_BFTCHC pfrrun_no_tool pfrrun plsql_run peicnt kkxexe opiexe kpoal8 opiodr kpoodr xupirtrc upirtrc kpurcsc kpuexecv8 kpuexec OCIStmtExecute kupprdp VInfreq__opirip opidrv sou2o opimai_real opimai BackgroundThreadSt art@4 77E64826

Cause

This is Bug 5879865.  It is fixed in the 11g release.

The problem is due to the query that uses an EXISTS sub-query involving a non-mergeable view causes the core dump.

Solution

1.  When available, download Patch 5879865 to resolve this issue.
      Note that this patch was not available at the time of writing this article ({JAN 2008}).

2. In the meantime, please use one of the following as a workaround:

•  set both of the following in the init/spfile
  • "_complex_view_merging" = false
  • event="38066 trace name context forever, level 1"  or level 2

       OR

• set  "_optimizer_cost_based_transformation"=off

Any of these workarounds can be done at the system level or set in the init or spfile file.


EXAMPLE:
===============

AT SYSTEM LEVEL:

SQL> alter system set events '38066 trace name context forever, level 2';
SQL> alter system set "_complex_view_merging" = false;
SQL> alter system set "_optimizer_cost_based_transformation"=off

IN THE INIT.ORA:
event="38066 trace name context forever, level 2"
_complex_view_merging=false
_optimizer_cost_based_transformation=off

IN THE SPFILE (requires restart of instance):
SQL> alter system set events '38066 trace name context forever, level 2' scope=spfile;
SQL> alter system set "_complex_view_merging" = false scope=spfile;
SQL> alter system set "_optimizer_cost_based_transformation"=off scope=spfile;

3.  Flush the shared pool or restart the database.

SQL>alter system flush shared_pool;

4.  Perform the Data Pump export again.