用 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'
)
)
