ORA-39095: Dump File Space Has Been Exhausted
Symptoms
The error message ORA-39095: Dump File Space Has Been Exhausted typically occurs during DataPump export when:
- Exporting large tables or data sets.
- The DataPump job stops unexpectedly due to insufficient dump file space.
- No trace files are generated at the time of the failure.
Cause
This error arises when the DUMPFILE parameter limits the number of files or when the FILESIZE parameter is too small. For example:
full=Y
directory=EXPORT_DIR
dumpfile=dump1.dmp,dump2.dmp,dump3.dmp
filesize=3G
In the above example, only 3 dump files with a maximum size of 3GB each are allowed, which might be insufficient for the export.
Solution
Here are several solutions to address the ORA-39095 error:
- Use a Dynamic File Format: Set the
DUMPFILE parameter dynamically using %U to allow up to 99 files.
dumpfile=dump_%U.dmp
- Increase File Size: Specify a larger
FILESIZE parameter to reduce the number of files required.
- Distribute Files Across Directories: Use multiple directories to store dump files, for example:
dumpfile=dir1:dump1_%U.dmp, dir2:dump2_%U.dmp
Examples
Below is an example of a corrected DataPump export command:
expdp system/password@database full=Y \
directory=EXPORT_DIR \
dumpfile=dump_%U.dmp \
filesize=5G \
logfile=export.log
This command ensures sufficient dump file space by dynamically creating additional dump files as needed.