When I look at the export log of daily routine schema exports, I saw that only one table skipped with error codeORA-31693: Table data object ****** failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists
I decided to take another data pump export for only the skipped table. After 30 seconds from expdp command data pump job took the error below
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job “SYSTEM”.”SYS_EXPORT_TABLE_03″ stopped due to fatal error at 23:51
I checked the free space on data_pump_dir and saw that there was enough space.
The jobs descriptions says the action for the solution is to reattach the job and add additional dump files.
Error: ORA-39095 (ORA-39095)
Text: Dump file space has been exhausted: Unable to allocate %s bytes
—————————————————————————
Cause: The Export job ran out of dump file space before the job was
completed.
Action: Reattach to the job and add additional dump files to the job
restarting the job.
We had to attach and add a file to continue. See steps below.
expdp system/***** attach=SYS_EXPORT_TABLE_03
Export: Release 10.1.0.4.0 – 64bit Production on Tuesday, 20 March, 2007 23:53
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Job: SYS_EXPORT_TABLE_03
Owner: SYSTEM
Operation: EXPORT
Creator Privs: FALSE
GUID: 2C22B38216C04297E044000E7FEDD1FD
Start Time: Tuesday, 20 March, 2007 23:53
Mode: TABLE
Instance: *****
Max Parallelism: 4
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** parfile=/backup/****/PARFILES/userdata_tablename.dat
DATA_ACCESS_METHOD AUTOMATIC
ESTIMATE BLOCKS
INCLUDE_METADATA 1
LOG_FILE_DIRECTORY DATA_PUMP_DIR2
LOG_FILE_NAME expdp_tablename_20032007.log
TABLE_CONSISTENCY 0
State: IDLING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Dump File: /backup/****/expdp_tablename_20032007.dmp
bytes written: 180,224
Worker 1 Status:
State: UNDEFINED
Worker 2 Status:
State: UNDEFINED
Object Schema: *****
Object Name: *****
Object Type: TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1
Worker 3 Status:
State: UNDEFINED
Worker 4 Status:
State: UNDEFINED
Export>add_file=expdp_tablename_20070320_2.dmp
Export> continue_client
Job SYS_EXPORT_TABLE_03 has been reopened at Tuesday, 20 March, 2007 23:53
Restarting “SYSTEM”.”SYS_EXPORT_TABLE_03″: system/******** parfile=/backup/******/PARFILES/userdata_tablename.dat
. . exported “********”.”*******” 72.46 GB 208475338 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_03″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_03 is:
/backup/*****/expdp_tablename_20070320.dmp
/backup/****/expdp_tablename_20070320_2.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_03″ completed with 1 error(s) at 00:47