解决 ORA-39095 ORA-3909 EXPDP 错误

OS环境：windows2008

数据库版本：oracle 11.2.0

使用工具：EXPDP

今天帮同事在测试库上导出两张表，第一张表大小为30M，第二章表大小为7G，以下是expdp语法

expdp tranuser/******** parallel=4 content=data_only directory=my_job dumpfile=wuqi02.dmp job_name=wuqi02 tables=(table_name)

第一张表很简单的导出完毕了，但是导出第二张表的时候则报错，报错内容如下

;;; 

Export: Release 11.2.0.1.0 - Production on Fri May 13 13:40:01 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

;;; 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

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

Starting "TRANUSER"."WUQI02":  tranuser/******** parallel=4 content=data_only directory=my_job dumpfile=wuqi02.dmp job_name=wuqi02 tables=(BOND_CB_YIELD_CUR_F) 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 7.062 GB

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Job "TRANUSER"."WUQI02" stopped due to fatal error at 13:40:20

Job WUQI02 has been reopened at Friday, 13 May, 2011 13:44 

Restarting "TRANUSER"."WUQI02":  tranuser/******** parallel=4 content=data_only directory=my_job dumpfile=wuqi02.dmp job_name=wuqi02 tables=(BOND_CB_YIELD_CUR_F) 

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

ORA-39065: unexpected master process exception in DISPATCH

ORA-39079: unable to enqueue message RP,KUPC$C_1_20110513134453,MCP,KUPC$A_1_20110513134602,1,Y

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

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

ORA-24033: no recipients for message

ORA-39065: unexpected master process exception in SEND

ORA-39079: unable to enqueue message RP,KUPC$C_1_20110513134453,MCP,KUPC$A_1_20110513134602,1,Y

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

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

ORA-24033: no recipients for message

Job "TRANUSER"."WUQI02" stopped due to fatal error at 13:46:03

Job WUQI02 has been reopened at Friday, 13 May, 2011 13:53 

ORA-39012: Client detached before the job started.

Job "TRANUSER"."WUQI02" stopped due to fatal error at 14:13:31

通过在网上查找相关的解决文档，我发现我这次的报错分为两个，一个是ORA-39095，另一个是ORA-39097，下面让我们来解决一下这个两个报错。

1：解决ORA-39095报错，出现该报错的原因一般分为两种情况。

第一种情况是因为在expdp的时候给才转储空间太小，数据无法写入转储文件内，所以报错，这种报错很好解决，只需要进入交互模式下，可以用add_file来增加转储文件，然后restart该job即可。

第二种情况则是因为设置参数parallel大于转储文件数目引起的，官方文档的解释是parallel io server processes写文件不能同时写一个，如果只有一个dumpfile(或少于parallel)就会影响性能。不但如此，当一个io server process在等待从而不能写dumpfile的时候就会报ORA-39095

解决该问题一共两种办法

1：进入到交互模式，减少parallel的数量，使其等于dmp文件的数量或增加dmp文件，使其与parallel数量相等。

2：在写导出语句的时候指定dumpfile中使用变量 %u(大小写均可)，让其自由分配转储文件即可。

2：解决ORA-39097报错

该报错我并没有查找到根本的原因，去metalink上看到有解决办法，则是加大streams_pool_size即可，导出恢复正常 alter system set streams_pool_size = 48M;