首页 » ORACLE 9i-23ai » How to stop or kill datapump jobs?(停止数据泵job)
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 参数
微信扫一扫，打赏作者吧～上一篇： 最近出差，至双节后
对不起，这篇文章暂时关闭评论。