IMPDP/EXPDP: Attaching to a running Job

IMPDP/EXPDP: Attaching to a running Job

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.

bash# sqlplus ‘/as sysdba’

SQL> select job_name, state from dba_datapump_jobs;

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

SQL> exit

bash# impdp \'scott/tiger\' attach=SYS_IMPORT_SCHEMA_02

You will be able to enter into the datapump import command prompt.

• This shows the status of your job)

           IMPORT> STATUS

• This will kill the datapump job and remove the underlying base tables

          IMPORT> KILL_JOB

• few other options while using datapump

          START_JOB, STOP_JOB, EXIT_CLIENT are .

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



bash# expdp \'scott/tiger\' attach=SYS_EXPORT_SCHEMA_01

Same as IMPDP.

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.

To Drop a table of Datapump running or orphaned jobs, please follow the link.

Cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS