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

;;;

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;

------------------------>>另外一贴
EXPDP/IMPDP进行Oracle数据迁移从高版本（11g）到底版本（10g）步骤 Oralce数据导入

可能产生的错误

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at 13:34:40

expdp时parallel不当也会引起ORA-39095
2008-09-24 15:01

在expdp做导出的时候会有碰到ora-39095的错误，引起这个错误的原因有两种。一一说来
先看官方的解释：
ORA-39095: Dump file space has been exhausted: Unable to allocate string bytes
Cause: The Export job ran out of dump file space before the job was completed.
Action: Reattach to the job and add additional dump files to the job restarting the job.
从字面意思就解释了第一种原因，那就是：空间不够了。解决方法也简单，多来点空间。
还有第二中原因：当使用了PARALLEL但是dumpfile却只有一个或小于parallel数，下面是官方的说明：

Oracle? Database Utilities
10g Release 2 (10.2)
2 Data Pump Export
PARALLEL=integer
The value you specify for integer should be less than, or equal to, the number of files in the dump file set (or you should specify substitution variables in the dump file specifications).
Because each active worker process or I/O server process writes exclusively to one file at a time, an insufficient number of files can have adverse effects. Some of the worker processes will be idle while waiting for files, thereby degrading the overall performance of the job.(第一个原因是影响性能)
More importantly, if any member of a cooperating group of parallel I/O server processes cannot obtain a file for output, then the export operation will be stopped with an ORA-39095 error. (ora-39095的成因)Both situations can be corrected by attaching to the job using the Data Pump Export utility, adding more files using the ADD_FILE command while in interactive mode, and in the case of a stopped job, restarting the job.
说白话点就是：parallel io server processes写文件不能同时写一个，如果只有一个dumpfile(或少于parallel)就会影响性能。不但如此，当一个io server process在等待从而不能写dumpfile的时候就会报ora-39095
要解决：expdp ATTACH 连上作业然后减少parallel或者增加dumpfile
从起源解决的话就是：指定parallel的时候就要指定至少同样多的dumpfile或者使用类似下面的命令(注意红字)：
expdp hr/hr DIRECTORY=dpump_dir1 LOGFILE=parallel_export.log JOB_NAME=par4_job DUMPFILE=par_exp%u.dmp PARALLEL=4

解决 ORA-39095 ORA-3909 EXPDP