I was running an export to get an estimate of the size of the dump file for the data in a list of tables. The parameter file contained the below options:
job_name=dean_expdp_data
estimate_only=yes
CONTENT=DATA_ONLY
DIRECTORY=DEAN_DATA_PUMP
PARALLEL=4
exclude=statistics
exclude=DB_LINK
exclude=tablespace
exclude=DIRECTORY
schemas=SCHEMA1,SCHEMA2,SCHEMA3
exclude=table:"IN('BIG_TABLE_01','BIG_TABLE_02')"
LOGFILE=dean_expdp_data.log
Ran the command
expdp \'/ as sysdba \' parfile=dean_expdp_data.par
and got error
[oracle@pdcigusprdb1 datapump]$ expdp \'/ as sysdba \' parfile=dean_expdp_data.par Export: Release 12.1.0.2.0 - Production on Fri Nov 17 19:01:54 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."DEAN_EXPDP_DATA": "/******** AS SYSDBA" parfile=dean_expdp_data.par Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION [TABLE_DATA:"SCHEMA"."TABLE_NAME"] ORA-31600: invalid input value for parameter TABLE_EXISTS_ACTION in function KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPW$WORKER", line 11259 ----- PL/SQL Call Stack ----- object line object handle number name 0x75a5a7aa0 27116 package body SYS.KUPW$WORKER 0x75a5a7aa0 11286 package body SYS.KUPW$WORKER 0x75a5a7aa0 20583 package body SYS.KUPW$WORKER 0x75a5a7aa0 15739 package body SYS.KUPW$WORKER 0x75a5a7aa0 10613 package body SYS.KUPW$WORKER 0x75a5a7aa0 13357 package body SYS.KUPW$WORKER 0x75a5a7aa0 17209 package body SYS.KUPW$WORKER 0x75a5a7aa0 5235 package body SYS.KUPW$WORKER 0x75a5a7aa0 12091 package body SYS.KUPW$WORKER 0x75a5a7aa0 2081 package body SYS.KUPW$WORKER 0x7174e4340 2 anonymous block KUPC$QUEUE_INT.SEND KUPC$QUEUE_INT.SEND Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA In procedure CREATE_TABLE_DATA_OBJECT_ROWS In function GATHER_PARSE_ITEMS In function CHECK_FOR_REMAP_NETWORK Nothing to remap In procedure BUILD_OBJECT_STRINGS In function PROCESS_TABLE_EXISTS_ACTION In procedure DETERMINE_FATAL_ERROR with ORA-31600: invalid input value for parameter TABLE_EXISTS_ACTION in function KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION Job "SYS"."DEAN_EXPDP_DATA" stopped due to fatal error at Fri Nov 17 19:01:56 2017 elapsed 0 00:00:02
According to Oracle Support “DataPump Export (EXPDP) Fails With Errors ORA-39126 ORA-31600 (Doc ID 1508068.1)”
use CONTENT=ALL instead of CONTENT=DATA_ONLY when using ESTIMATE_ONLY=Y parameter
I changed parameter file and ran the export and got the error
Export: Release 12.1.0.2.0 - Production on Fri Nov 17 19:42:50 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options ORA-31626: job does not exist ORA-31633: "SYS.DEAN_EXPDP_DATA" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 1048 ORA-00955: name is already used by an existing object
As the previous export had failed, I had to clean up dba_datapump_jobs:
SQL> SELECT owner_name, job_name, operation, job_mode, 2 state, attached_sessions 3 FROM dba_datapump_jobs where owner_name='SYS' and state='NOT RUNNING' ORDER BY 1,2; 4 5 OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS SYS DEAN_EXPDP_DATA EXPORT SCHEMA NOT RUNNING 0
Drop the table:
SQL> drop table DEAN_EXPDP_DATA; Table dropped. SQL>
Reran the export and got the estimate. However, it sort of defeated the purpose as CONTENT=ALL includes the meta data in the size of the export.
Total estimation using BLOCKS method: 596.3 MB Job "SYS"."DEAN_EXPDP_DATA" successfully completed at Fri Nov 17 19:47:31 2017 elapsed 0 00:00:02