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

今天到表的时候遇到这么一个怪问题。表的大小明明只有2G,设置的dumpfile文件为5G，足足有余了。可是导出表时却一直报ora-39095这个问题。

导出文件脚本

userid='/ as sysdba'
dumpfile=20121015cif2dev2.dmp
directory=DATA_PUMP_DIR
filesize=10000M
logfile=20121015cif2dev2.log
parallel=4
tables=CIF2LOGTMP.WEISHUPENG_GET_PARTY_VIP_1
job_name=xionglang619dump
content=all

执行：

nohup expdp parfile=20121015cif2dev2.par &  放入后台进行执行。

结果就处理这个错误。很是郁闷啊！为啥呢？

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "SYS"."XIONGLANG619DUMP" stopped due to fatal error at 14:09:01

 

测试了下去掉parallel后执行可以成功。为啥加入并行就会出错呢？？

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. 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.

You can supply multiple file_name specifications as a comma-delimited list or in separate DUMPFILE parameter specifications. If no extension is given for the filename, then Export uses the default file extension of .dmp. The filenames can contain a substitution variable (%U), which implies that multiple files may be generated. The substitution variable is expanded in the resulting filenames into a 2-digit, fixed-width, incrementing integer starting at 01 and ending at 99. If a file specification contains two substitution variables, both are incremented at the same time. For example, exp%Uaa%U.dmp would resolve to exp01aa01.dmp, exp02aa02.dmp, and so forth.

在oracle的文档中看到了并行的影响。

文章说的很清楚了。所以就用%U作为dumpfile文件。这样就没有问题了。

userid='/ as sysdba'
dumpfile=20121015cif2dev2%U.dmp
directory=DATA_PUMP_DIR
filesize=10000M
logfile=20121015cif2dev2.log
parallel=4
tables=CIF2LOGTMP.WEISHUPENG_GET_PARTY_VIP_1
job_name=xionglang619dump1
content=all

这样就一点问题就没有了。nohup expdp parfile=20121015cif2dev2.par &

 

总结：oracle在处理大表时是将表分成多个分块，从而实现并行查询。