ORA-39095: Dump file space has been exhausted | filesize parameter

I got ” ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes ” error in the Oracle database.

 

ORA-39095: Dump file space has been exhausted

Details of error are as follows.

connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_07": "/******** AS SYSDBA" directory=pump_dir dumpfile=TableBackup%U.dmp logfile=TableBackup.log parallel=32 cluster=n exclude=statistics tables=MSDBA.TEST_TABLE query=MSDBA.TEST_TABLE:"WHERE ACTIVITY_DT_MONTH_CD>202104" COMPRESSION=ALL
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "SYS"."SYS_EXPORT_TABLE_07" stopped due to fatal error at Tue Jul 27 09:06:52 2021 elapsed 0 00:00:52
~ 

 

 

 

Unable to allocate 8192 bytes

This ORA-39095 errors are related with the limited the number of files in DUMPFILE parameter with limited FILESIZE , i.e.:

full=Y
directory=<DIRECTORY_NAME>
dumpfile=<DUMP_NAME>_1.dmp,<DUMP_NAME>_2.dmp,<DUMP_NAME>_3.dmp,<DUMP_NAME>_4.dmp
filesize=3000000000

This means max. 4 dump files with file size 3000000000 bytes (4×2.79 GB).

 

To solve this error, use the filesize parameter in the expdp command as follows.

 

filesize=100M

 

expdp \"/ as sysdba\" directory=pump_dir dumpfile=TableBackup%U.dmp logfile=TableBackup.log parallel=32 cluster=n exclude=statistics tables=MSDBA.TEST_TABLE query=MSDBA.TEST_TABLE:"WHERE ACTIVITY_DT_MONTH_CD>202104" COMPRESSION=ALL filesize=100M

 

OR perform the following steps.

1. Use the dynamic format  (i.e dumpfile=<DUMP_NAME>_%U.dmp) :

The ‘wildcard’ specification for the dump file can expand up to 99 files. If 99 files have been generated before the export has completed, it will again return the ORA-39095 error.

2. To avoid the ORA-39095 error by reducing number of files needed, a workaround would be to specify a bigger FILESIZE parameter.

3. If this is inconvenient, then another option is to use this syntax:

 

dumpfile=<DUMP_NAME>1_%U.dmp, <DUMP_NAME>2_%U.dmp, <DUMP_NAME>3_%U.dmp

which can expand up to 3*99 files.

If encountering problems containing the dump in a single directory using this solution, you may prefer this syntax:

 

 

dumpfile=<DIR_NAME1>:<DUMP_NAME>1_%U.dmp, <DIR_NAME2>:<DUMP_NAME>2_%U.dmp, <DIR_NAME3>:<DUMP_NAME>3_U.dmp

(assuming the 3 directory objects listed above had been already created first).

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )