How to Fix ORA-39095: Dump File Space Has Been Exhausted

To fix the ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes error, you can follow these steps:

1. Use Dynamic Dump File Names with Wildcard %U

The primary solution is to use a dynamic dump file name with the %U wildcard. This allows Data Pump to automatically create multiple dump files, splitting the export job across several smaller files and ensuring you don’t run out of space.

Example:

expdp your_user/password DIRECTORY=your_directory DUMPFILE=my_export_files_%U.dmp LOGFILE=my_export_log.log

In this case:

• Data Pump will create multiple dump files with names like my_export_files_01.dmp, my_export_files_02.dmp, and so on.
• It will automatically distribute the data across multiple files, preventing the dump file from exceeding disk space.

2. Limit the Size of Each Dump File (Optional)

You can also limit the size of each dump file using the FILESIZE parameter. This ensures that no single dump file becomes too large, which can help manage disk space more efficiently.

Example:

expdp your_user/password DIRECTORY=your_directory DUMPFILE=my_export_files_%U.dmp LOGFILE=my_export_log.log FILESIZE=2G

This will split the export into files, each of a maximum size of 2GB.

3. Check Disk Space

Ensure that the directory specified for the dump files has enough available disk space. If necessary, free up space or specify a different directory that has sufficient capacity.

You can check the available disk space on the server using commands like:

• Linux/Unix: df -h
• Windows: dir

If the disk is full, free up some space or change the dump directory to another location with more available space.

4. Check Temporary Tablespace

If there is insufficient space in the temporary tablespace, it may also cause the error. Make sure there is enough space in the temporary tablespace used by Data Pump for sorting operations. You can query the available space in the temporary tablespace with the following SQL:

SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 / 1024
mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

If necessary, add more space to the temporary tablespace:

ALTER DATABASE TEMPFILE '/path/to/tempfile.dbf' RESIZE 2G;

5. Monitor and Adjust Resource Usage

• Use the PARALLEL parameter to improve the performance of the export operation and reduce the space required by spreading the load across multiple processes.
• You can also use the COMPRESSION parameter to reduce the size of the dump files during the export process.

Example with PARALLEL:

expdp your_user/password DIRECTORY=your_directory DUMPFILE=my_export_files_%U.dmp LOGFILE=my_export_log.log PARALLEL=4

By using the dynamic dump file names with %U, ensuring sufficient disk and temporary tablespace, and optionally limiting the file size with FILESIZE, you can resolve the ORA-39095 error effectively