[分享]How to stop or kill datapump jobs?_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2203 | 回复: 0

主题： [分享]How to stop or kill datapump jobs?

无止境

注册用户

等级：新兵
经验：64
发帖：74
精华：0
注册：2011-11-14
状态：离线
发送短消息息给无止境

加好友发送短消息息给无止境

发消息

发表于：

2014-12-25 14:39:01 | [全部帖] [楼主帖]

楼主

今天开发需求从生产库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 参数

--转自