Skip navigation

Whenever you run a datapump job, a table is created in the schema of the user with whom you launched datapump. That table is called the MASTER TABLE, and takes the name of your JOB_NAME:

expdp system/Admin123@PDBorcl1644 directory=EXP_DIR schemas=oe dumpfile=PDBORCLoe%U.dmp parallel=2 REUSE_DUMPFILES=YES KEEP_MASTER=YES JOB_NAME=THATSMYJOBNAMEFORMYEXPORT

 

SYSTEM > select table_name from user_tables where table_name like '%EXPORT%';
TABLE_NAME                 
THATSMYJOBNAMEFORMYEXPORT

 

But if you run a datapump job without a job_name, the master table will bear a system-generated name:

SYS_EXPORT_SCHEMA_01 or SYS_IMPORT_SCHEMA_01 for "SCHEMAS=" jobs

SYS_IMPORT_FULL_01 or SYS_EXPORT_FULL_01 for "FULL=YES" jobs

 

Unless otherwise indicated (with the KEEP_MASTER=YES parameter), that MASTER TABLE is dropped at the end of a succesful datapump job.  In case of a failed job, however, that MASTER TABLE remains.  So if your daily datapump job is not monitored and keeps failing every day, you will get a new MASTER TABLE per day, with an incremented suffix.

 

Now, let's say that our "FULL=YES" EXPDP  job has failed 53 times.  We  now have 53 of those MASTER TABLES.  Interestingly, if we drop the SYS_EXPORT_FULL_49 MASTER TABLE:

 

SYS AS SYSDBA> drop table sys.SYS_EXPORT_FULL_49 ;

 

49ismissing.jpg

the next time our EXPDP job runs, it will not create the SYS_EXPORT_FULL_54 table, but will re-create our dropped SYS_EXPORT_FULL_49 table:

49isback.png

 

But even more worth of note is that if our datapump job fails 100 times, Oracle does not create SYS_EXPORT_FULL_100 and generates an error

ORA-31626: job does not exist

99.jpg

Conclusions:

  1. let's always monitor our datapump jobs
  2. I recommend using a JOB_NAME with a variable so that the JOB NAME, and thus the MASTER TABLE, will always have a different name.  Examples:

          JOB_NAME=importof${2}${3}into${4}${5} in Unix

         JOB_NAME=refresh%date% in Windows