How to Cleanup Oracle Datapump Import Jobs?

kpalati 6:57 am on October 26, 2009 | Answers: 1    
Tags: base tables, cleanup (2), datapump (2), db objects, extension (9), import (12), job, jobs, kill (2), oracle (120), oracle database datapump import kill, oracle import, select job, sql (61), sqlplus (7)

Rate Knowledge: (1 votes)

 Loading ...

• kpalati 9:31 pm on October 26, 2009
  

  Occasionally we might want to kill a long running Oracle import jobs ,we do it using kill -p But when this (kill -p) is done for a datapump job, it doesn’t completely kill the job and locks up the underlying db objects. To resolve this, you must attach to a datapump job and use kill_job

  UNIX\> sqlplus ‘/as sysdba’

  SQL> select job_name, state from dba_datapump_jobs;

  JOB_NAME STATE
  —————————— ——————————
  SYS_IMPORT_SCHEMA_01 NOT RUNNING

  SQL> exit

  UNIX\> impdp \’sys/aa as sysdba\’ attach=SYS_IMPORT_SCHEMA_01

  IMPORT> STATUS –> This shows the status of your job

  IMPORT> KILL_JOB –> This will kill the datapump job and remove the underlying base tables

  START_JOB, STOP_JOB, EXIT_CLIENT are few other options while using datapump.

  impdp help=y or expdp help=y lists all of them

  Due to some locking conditions, if you are not able to kill the job using above method, you can also drop the datapump master table – pls make sure you are dropping the right table as this is irreversible.

  UNIX\> sqlplus ‘/as sysdba’
  SQL> drop table SYS_IMPORT_SCHEMA_01;
  SQL> exit