Pages

Tuesday, 22 November 2011

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



ORA-39095 error in Datapump export when using PARALLEL option and single dumpfile

Environment:
10g R2 in Linux Box

I tried to export the database using the below par file, It has only single dumpfile.

USERID=username/*****
DUMPFILE=expdp.stg2514.dmp
FULL=y
DIRECTORY=DATA_PUMP_DIR1
JOB_NAME=expfull_stg2514_new
parallel=5

It ended up with the below error:
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "SYSTEM"."EXPFULL_
stg2514_NEW" stopped due to fatal error at 03:46:40

I googled to get a good solution to overcome the above error, many suggested to remove the parallel option. Then I ran the export by removing the parallel option. It completed successfully without errors. But I did not did not understand why the error occurred if the parallel option is used. I searched many sites for the cause and at last I familiarized that it is a bug (Bug:3328558) in 10g R2, mentioned in Metalink.

Cause:

If the dumpfiles are less than the parallel processes, then the dumpfile may be locked by one process and the other process waits for the dumpfile to write. And sometimes the process may not release the lock even if the process finished. So the entire task will not end as the other processes are waiting for the lock.

Solution:

Parallelism should be less than or equal to the number of the dumpfiles. Or avoid the usage of parallel option.


    2 comments:

    1. Really helpful, I was creating a dumpfile with 'parallel' = 4 and only 3 dumpfilenames specified in the 'dumpfile', and getting the above error.

      ReplyDelete
    2. Thanks for the above solution. It was very much helpful.

      ReplyDelete