清除 Oracle Data Pump Job

步驟 1：找出正在執行的 JOB


用 system 帳號登入 oracle 並利用下面 SQL 指令

SQL> select job_name, state from dba_datapump_jobs;

JOB_NAME         STATE
------------------------------ ------------------------------
SYS_IMPORT_SCHEMA_02        EXECUTING
SYS_EXPORT_SCHEMA_01        NOT RUNNING
SYS_IMPORT_SCHEMA_01        NOT RUNNING
SYS_EXPORT_SCHEMA_02        NOT RUNNING

看到 STATE = EXECUTING 則代表此 JOB 正在執行中，因此我們可以先將 JOB_NAME 記下來

此例子 JOB_NAME = SYS_IMPORT_SCHEMA_02 

步驟 2：執行 Impdp utility

   impdp system/******* attach=system.SYS_IMPORT_SCHEMA_02 

system/******* ==> 代表 system 之帳號及密碼

執行結果：

Import: Release 11.2.0.3.0 - Production on Sab May 19 21:55:38 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Job: SYS_IMPORT_SCHEMA_02
  Owner: system
  Operation: IMPORT
  Creator Privs: TRUE
  GUID: C06A6B4EAEB4122ER0434EDH74850F
  Start Time: Sabado, 19 Mayo, 2012 21:55:04
  Mode: SCHEMA
  Instance: BD1
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** SCHEMAS=SCHEMA DIRECTORY=BCK dumpfile=export_SCHEMA.dmp logfile=export.SCHEMA.log reuse_dumpfiles=true
  IMPORT Job Parameters:
     CLIENT_COMMAND        system/******** SCHEMAS=SCHEMA DIRECTORY=BCK dumpfile=export_SCHEMA.dmp logfile=import_SCHEMA.log
  State: EXECUTING
  Bytes Processed: 90.681.272
  Percent Done: 98
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /bck/export_SCHEMA.dmp

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: SCHEMA
  Object Name: TABLE_EXAMPLE
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 28
  Completed Bytes: 90.832
  Worker Parallelism: 1

Import> 

亦可透過 STATUS 指令觀察該 Job 之狀態：

Import> STATUS
Job: SYS_IMPORT_SCHEMA_02
  Operation: IMPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 91.676.344
  Percent Done: 99
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /bck/export_SCHEMA.dmp

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: SCHEMA
  Object Name: TABLE_OBJECTS_EXAMPLE
  Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
  Completed Objects: 38
  Worker Parallelism: 1

步驟 3：刪除 Job

Import> kill_job
Are you sure you wish to stop this job ([yes]/no):

 
步驟 4：刪除 Table (若 Table 還在)

sqlplus / as sysdba
SQL> drop table SYS_IMPORT_SCHEMA_02;
Table dropped. 

參考資料：http://albertolarripa.com/2012/05/19/clean-oracle-data-pump-jobs/

How to delete/remove non executing datapump jobs?
https://pavandba.com/2011/07/12/how-to-deleteremove-non-executing-datapump-jobs/

Kill, cancel and resume or restart datapump expdp and impdp jobs
http://blog.oracle48.nl/wordpress/killing-and-resuming-datapump-expdp-and-impdp-jobs/

Find DB Session and DB Process by Server Process ID  (SPID)
--Find Session by SPID (Server Process ID)
SELECT
     S.SID,
     S.SERIAL#,
     S.PROCESS,
     P.SERIAL# PROCESS_SERIAL#,
     P.PID,
     P.SPID,
     S.LOCKWAIT,
     S.STATUS,
     S.LOGON_TIME,
     S.MODULE,
     S.ACTION,
     S.MACHINE
FROM
     V$SESSION S,
     V$PROCESS P
WHERE 1 = 1
AND S.PADDR = P.ADDR
AND (
     P.SPID IN (
          '21581','21674','23559'
     )
)