Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cannot remove or attach to old IMPDP job, with solution

user10635287Jul 17 2018

I am doing an upgrade to 12.2. It dies on an old datapump import that died with the server crashing over a year ago.

I queried the dba_datapump_jobs table and it is there.

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE
------------------------------
SYSTEM
SYS_IMPORT_FULL_01
IMPORT

Tried to attach to it to kill it, and it fails with a cannot find file, so I created a new one from the original database it was created from with a dummy table. It completed successfully, now it says the file is owned by an export that doesn't exist:

oracle@etestdbop1: /home/oracle => impdp system attach=SYS_IMPORT_FULL_01

Import: Release 12.1.0.2.0 - Production on Mon Jul 16 08:30:14 2018

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-39002: invalid operation
ORA-39000: bad dump file specification
ORA-39140: dump file "/mnt/dbashare/etm_prod_cutover.dmp" belongs to job "SYSTEM"."SYS_EXPORT_TABLE_01"

oracle@etestdbop1: /home/oracle => expdp system attach=SYS_EXPORT_TABLE_01

Export: Release 12.1.0.2.0 - Production on Mon Jul 16 08:32:49 2018

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-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 405
ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user SYSTEM
ORA-31632: master table "SYSTEM.SYS_EXPORT_TABLE_01" not found, invalid, or inaccessible
ORA-00942: table or view does not exist

And the supposed export job is not in the dba_datapump_jobs table.

So I looked for the job again to delete the job table and release it:

SQL> SELECT o.status,o.object_id,o.object_type,o.owner||'.'||object_name as "OWNER.OBJECT"
FROM dba_objects o,dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2; 2 3

STATUS OBJECT_ID OBJECT_TYPE
------- ---------- -----------------------
OWNER.OBJECT
--------------------------------------------------------------------------------
VALID 91185 TABLE
SYSTEM.SYS_IMPORT_FULL_01

And was able to drop the table.

SQL> DROP TABLE SYSTEM.SYS_IMPORT_FULL_01;

Table dropped.

SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs; 2 3

no rows selected

I hope this helps someone.

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 14 2018

Added on Jul 17 2018

#database-key-features, #export-import-sql-loader-external-tables

0 comments

5,641 views