How to stop or kill datapump jobs?(停止数据泵job)

今天开发需求从生产库down个库到测试库，我拿了个离线备份文件(expdp)导入，需求是只用一个ICME SCHEMA即可，在impdp时忘了expdp是个full，有其它schemes存在备份文件中

[oracle@dev-db datapump]$ ll -h
total 12G
-rw-rw----  1 oracle oracle  12G Sep 10 05:07 192.168.212.56_icme.2012-09-10.dmpdp
[oracle@dev-db datapump]$ impdp system/oracle directory=icme dumpfile=192.168.212.56_icme.2012-09-10.dmpdp remap_schema=icme:icme4 remap_tablespace=icme:icmetbs remap_tablespace=users:icmetbs

开始后才发现，错了，导入的默认全部，如果时间可以接受也就算了，导入后再删，检查一下

SQL> l
  1* select opname,start_time,elapsed_seconds,(totalwork-sofar)/sofar*elapsed_seconds from v$session_longops
SQL> /
OPNAME                         START_TIME          ELAPSED_SECONDS (TOTALWORK-SOFAR)/SOFAR*ELAPSED_SECONDS
------------------------------ ------------------- --------------- ---------------------------------------
SYS_IMPORT_FULL_03             2012-09-17 10:29:03            4530                              992.331906
Sort Output                    2012-09-17 10:35:09              15                                       0
Table Scan                     2012-09-17 10:38:02              11                                       0
Table Scan                     2012-09-17 10:41:39             106                                       0
Sort Output                    2012-09-17 10:43:25             245                                       0
Index Fast Full Scan           2012-09-17 10:47:30              35                                       0
Sort Output                    2012-09-17 10:48:05              87                                       0
Index Fast Full Scan           2012-09-17 10:49:32              24                                       0
Sort Output                    2012-09-17 10:49:56              42                                       0
Table Scan                     2012-09-17 10:50:40              61                                       0
Sort Output                    2012-09-17 10:51:43             112                                       0
Index Fast Full Scan           2012-09-17 10:53:35              27                                       0
Sort Output                    2012-09-17 10:54:02              69                                       0
Index Fast Full Scan           2012-09-17 10:55:11              31                                       0
Sort Output                    2012-09-17 10:55:42              90                                       0
Index Fast Full Scan           2012-09-17 10:57:12              33                                       0
Sort Output                    2012-09-17 10:57:48              55                                       0
Table Scan                     2012-09-17 10:58:43              38                                       0
Sort Output                    2012-09-17 10:59:21              19                                       0
Sort Output                    2012-09-17 10:59:45              13                                       0
Table Scan                     2012-09-17 10:59:58              62                                       0
Sort Output                    2012-09-17 11:01:00             103                                       0
Index Fast Full Scan           2012-09-17 11:04:01              11                                       0
Index Fast Full Scan           2012-09-17 11:04:14               7                                       0
SYS_IMPORT_FULL_04             2012-09-17 10:29:31            4484                              20778.1593
25 rows selected.
SQL> SELECT * FROM DBA_DATAPUMP_SESSIONS;
OWNER_NAME                     JOB_NAME                       SADDR            SESSION_TYPE
------------------------------ ------------------------------ ---------------- --------------
SYSTEM                         SYS_IMPORT_FULL_04             000000021B3475F0 DBMS_DATAPUMP
SYSTEM                         SYS_IMPORT_FULL_03             000000021A330A00 MASTER
SYSTEM                         SYS_IMPORT_FULL_03             000000021B339788 WORKER
SYSTEM                         SYS_IMPORT_FULL_04             000000021A308300 MASTER
SYSTEM                         SYS_IMPORT_FULL_04             000000021B335AE0 WORKER
tip :
session_type reference  http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3085.htm
确认当前的运行job，注意不是dba_jobs_running,也不是v$scheduler_running_jobs
SQL> select * from dba_datapump_jobs where state='EXECUTING';
OWNER_NAME                     JOB_NAME                       OPERATION                                                    JOB_MODE
------------------------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ---------- ----------------- -----------------
SYSTEM                         SYS_IMPORT_FULL_03             IMPORT                                                       FULL
EXECUTING                               1                 0                 2
SYSTEM                         SYS_IMPORT_FULL_04             IMPORT                                                       FULL
EXECUTING                               1                 1                 3

TIP:
其中有一个是我刚开始，按了CTRL +C ，并未回收所以SYS_IMPORT_FULL_03应该是那个事务
估算的SYS_IMPORT_FULL_04 剩余20778.1593秒，无法接受，下面停掉该JOB,此处不用OS 的kill 暴力结束

SQL> DECLARE 
  2  hdl number;
  3  begin
  4  hdl := dbms_datapump.attach('SYS_IMPORT_FULL_03','SYSTEM');
  5  DBMS_DATAPUMP.STOP_JOB(hdl,1,0);
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL> l4
  4* hdl := dbms_datapump.attach('SYS_IMPORT_FULL_03','SYSTEM');
SQL> c/_03/_04/
  4* hdl := dbms_datapump.attach('SYS_IMPORT_FULL_04','SYSTEM');
SQL> l
  1  DECLARE
  2  hdl number;
  3  begin
  4  hdl := dbms_datapump.attach('SYS_IMPORT_FULL_04','SYSTEM');
  5  DBMS_DATAPUMP.STOP_JOB(hdl,1,0);
  6* end;
SQL> /
PL/SQL procedure successfully completed.

--观察dba_datapump_jobs 的状态值有EXECUTING--->STOPPING--->NOT RUNNING,结束后IMPDP加schemas 参数