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 )

 952 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply