advertisements
_____________________________________________________________________________________________________________________
Error Description:
Datapump EXPDP export is failed with following error.
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Solution Description:
This error comes when you specify the FILESIZE or parallel option with datapump EXPDP. If you specify above parameters the EXPDP normally creates multiple files according to the FILESIZE or the number of processes. Then you should be careful about the DUMPFILE parameter value as you should ‘%U’ with the parameter value. This is a kind of bug with expdp. These are the possible solutions which you can use.
- Specify the number of dumpfiles as much you have in the parallel clause
- Remove parallel clause if your expected job is a smaller one.
- Mention the %U clause with your dumpfile parameter.
See some examples below for Error
Scenario 1. Error with PARALLEL clause
expdp job_name=query_export tables=TEST.OBJECT_LIST directory=EXP_DIR dumpfile=OBJECT_LIST.dmp parallel=4
Export: Release 11.2.0.2.0 - Production on Mon Feb 20 01:36:03 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."QUERY_EXPORT": /******** AS SYSDBA job_name=query_export tables=TEST.OBJECT_LIST directory=EXP_DIR dumpfile=OBJECT_LIST.dmp parallel=4
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.678 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "SYS"."QUERY_EXPORT" stopped due to fatal error at 01:36:16
Scenario 2. Error with FILESIZE clause
expdp job_name=query_export1 filesize=1m tables=TEST.OBJECT_LIST directory=EXP_DIR dumpfile=OBJECT_LIST.dmp
Export: Release 11.2.0.2.0 - Production on Mon Feb 20 01:56:28 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."QUERY_EXPORT1": /******** AS SYSDBA job_name=query_export1 filesize=1m tables=TEST.OBJECT_LIST directory=EXP_DIR dumpfile=OBJECT_LIST.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.678 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes
Job "SYS"."QUERY_EXPORT1" stopped due to fatal error at 01:56:37
Resolution Example:
expdp job_name=query_export2 parallel=4 tables=TEST.OBJECT_LIST directory=EXP_DIR dumpfile=OBJECT_LIST%u.dmp
Export: Release 11.2.0.2.0 - Production on Mon Feb 20 02:54:25 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."QUERY_EXPORT2": /******** AS SYSDBA job_name=query_export2 parallel=4 tables=TEST.OBJECT_LIST directory=EXP_DIR dumpfile=OBJECT_LIST%u.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.678 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."OBJECT_LIST" 2.295 GB 24718848 rows
Master table "SYS"."QUERY_EXPORT2" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.QUERY_EXPORT2 is:
/home/oracle/exp/OBJECT_LIST01.dmp
/home/oracle/exp/OBJECT_LIST02.dmp
/home/oracle/exp/OBJECT_LIST03.dmp
/home/oracle/exp/OBJECT_LIST04.dmp
Job "SYS"."QUERY_EXPORT2" successfully completed at 02:56:57
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment