Saturday, January 4, 2014

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

Today while running one Export job , got below issue . 

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "XXX"."XML_DOCUMENT"           3.716 GB 1501732 rows
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at 20:22:02
Job SYS_EXPORT_SCHEMA_01 has been reopened at Friday, 03 January, 2014 20:27
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at 20:27:11

System  and all TBS and MP has sufficient space .  after doing some test came to know issue was -

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.
To avoid above confussion i use dumpfile=dumpfile_name_%U.dmp parallel=number of CPU process/2,3.4 ..and my job went fine which any issue .



No comments:

Post a Comment