Have you received “ORA-39034: Table TABLE_DATA: X does not exist“, while executing an import operation?
Were you scratching your head, to actually figure out the problem?
Well, this is exactly what happened to me the other day. Let me give you some context here, so you better understand the problem and the solution.
A user was asking me to copy SCOTT.EMPLOYEES table from the production database into the development database, and instead of naming it SCOTT.EMPLOYEES, the table should be named SCOTT.EMPLOYEES_2020.
The requirements were to not bring over constraints, and only copy the data. At this point you probably realize that I am giving you not the real table names.
Looking at the requirements, my first thought was to use the following parameters in the export datapump parameter file:
remap_tables, exclude=constraint, content=data_only. These three parameters would achieve the required results! The remap_table, would take care of the table’s new name, the exclude constraints would take care of not bringing over the constraints, and the content=data_only, would take care of only bringing over the data.
The export parameter file looked like this:
expdp_hrprd_scott.employees.par
DIRECTORY=DATA_PUMP_DIR
REUSE_DUMPFILES=YES
DUMPFILE=expdp_hrprd_scott.employees.dmp
LOGFILE=expdp_hrprd_scott.employees.log
TABLES=scott.employees
EXCLUDE=CONSTRAINT
CONTENT=DATA_ONLY
Running the export:
expdp parfile=expdp_hrprd_scott.employees.par
The export ran successfully, no errors encountered.
Next step was to import the table into the development database, with the following parameter file:
impdp_hrdev_scott.employees.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=expdp_hrprd_scott.employees.dmp
LOGFILE=impdp_hrdev_scott.employees.log
TABLES=scott.employees
REMAP_TABLE=scott.employees:employees_2020
Running the import:
impdp parfile=impdp_hrdev_scott.employees.par
The import starts running, and all of a sudden the following error is raised:
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39034: Table TABLE_DATA:"SCOTT"."EMPLOYEES_2020" does not exist.
And this is the moment I started scratching my head. Because of course the table doesn’t exist, this is a new table!
Then it dawned on me, where the mistake is, and what the solution is!
You see, Oracle is indeed giving you the right error message! The SCOTT.EMPLOYEES_2020 table doesn’t exist in the development database. I exported the data from production, with the option of DATA_ONLY.
This means, no metadata about the table is exported, which means there is no table DDL in the export dump file. So on the import, the new table cannot be created, because there is no DDL, and this table doesn’t exist in the database, just like the error message suggested!
Once I knew the cause of the error, finding the solution was easy! Just removed the content=data_only from the expdp par file, then the metadata was also exported!
The new expdp parfile looked like this:
DIRECTORY=DATA_PUMP_DIR
REUSE_DUMPFILES=YES
DUMPFILE=expdp_hrprd_scott.employees.dmp
LOGFILE=expdp_hrprd_scott.employees.log
TABLES=scott.employees
EXCLUDE=CONSTRAINT
The impdp par file looked the same. The second time when I ran the import, the new table was created with the new name, and the data imported.
There was another challenge that was happening with the indexes, but I’ll keep that for next week!
