oracle提示39095,expdp 引起ORA-39095

今天expdp导出表的时候出现问题：

[oracle@busdbr1] /busdmp 541> expdp system/xxx DIRECTORY =BUS_DMP tables=business.prpcinsured DUMPFILE=exp111.dmp logfile=exp111.log PARALLEL=4;

Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 18 January, 2012 16:16:21

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

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

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

and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_04":  system/******** DIRECTORY =BUS_DMP tables=business.prpcinsured DUMPFILE=exp111.dmp logfile=exp111.log PARALLEL=4

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 4.492 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

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

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

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

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

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

Job "SYSTEM"."SYS_EXPORT_TABLE_04" stopped due to fatal error at 16:16:45

--于是我查了一下：提示我转储空间不够。

SQL> host oerr ora 39095

39095, 00000, "Dump file space has been exhausted: Unable to allocate %s 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.

--进入转储目录查了一下：空间完全没有问题。还剩100g

--网上查了一下。原话：

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

[oracle@busdbr1] /busdmp 545> expdp system/oracle DIRECTORY =BUS_DMP tables=business.prpcinsured DUMPFILE=exp111.dmp logfile=exp111.log

Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 18 January, 2012 16:20:10

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

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

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

and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_05":  system/******** DIRECTORY =BUS_DMP tables=business.prpcinsured DUMPFILE=exp111.dmp logfile=exp111.log

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 4.492 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

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

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

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

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "BUSINESS"."PRPCINSURED"                    3.684 GB 30449560 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_05" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_05 is:

/busdmp/exp111.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_05" successfully completed at 16:23:17