合 在Oracle中，如何彻底停止expdp或impdp进程？
Tags： Oracle故障处理数据泵杀会话expdpimpdp杀进程
👉 本文共约3762个字，系统预计阅读时间或需15分钟。
许多同事在使用expdp或impdp命令时，不小心按了CTRL+C组合键，然后又输入exit命令（或者网络中断等异常现象），导致expdp或impdp进程不存在，但Oracle数据库的会话仍存在，所以dmp文件也一直在增长（或数据一直在导入到数据库中）。
处理过程
在这种情况下的处理办法如下所示：
1、检查expdp进程是否还在
1 2 | ps -ef | grep expdp ps -ef | grep impdp |
若存在，则可用kill -9 process命令杀掉expdp或impdp的进程。
2、杀会话、删表
检查会话是否仍存在，若存在则把相关的会话杀掉（注意：先使用命令“ALTER SYSTEM KILL SESSION '22,33' immediate;”在数据库级别杀掉会话，然后在OS级别使用kill -9杀掉进程），如无杀会话的权限则可以将相关的表DROP掉，表名可以使用如下的SQL来查询：
1 2 3 4 5 6 7 8 9 10 | -- pdb或非cdb SELECT * FROM DBA_DATAPUMP_SESSIONS; SELECT * FROM DBA_DATAPUMP_JOBS; SELECT 'drop table '||d.OWNER_NAME||'.'||JOB_NAME||' purge;' FROM DBA_DATAPUMP_JOBS d where STATE='NOT RUNNING'; -- cdb查询 SELECT * FROM CDB_DATAPUMP_SESSIONS; SELECT * FROM CDB_DATAPUMP_JOBS; |
例如：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SYS@orclasm > SELECT * FROM DBA_DATAPUMP_SESSIONS; OWNER_NAME JOB_NAME INST_ID SADDR SESSION_TYPE ---------- ------------------------- ---------- ---------------- -------------- LHR SYS_EXPORT_SCHEMA_04 1 00000000A8B71D98 MASTER LHR SYS_EXPORT_SCHEMA_04 1 00000000AB98AFC8 WORKER SYS@orclasm > DROP TABLE LHR.SYS_EXPORT_SCHEMA_04 PURGE; Table dropped. SYS@orclasm > SELECT * FROM DBA_DATAPUMP_SESSIONS; no rows selected SYS@orclasm > SELECT * FROM DBA_DATAPUMP_JOBS; no rows selected |
使用相同的办法也删除从视图DBA_DATAPUMP_JOBS中查询出来的表，直到2个视图无记录。
3、删除导出的dmp文件。
如不删除，则重新执行expdp命令时，会报dmp文件已存在。
使用kill_job停止
如果没有退出expdp或impdp会话，则可以输入kill_job来直接停止导出导入进程也是可以的。
1 2 3 4 5 | expdp \'/ AS SYSDBA\' attach=SYS_EXPORT_SCHEMA_03 Export> kill_job Are you sure you wish to stop this job ([yes]/no): yes |
若是已经退出会话，则也可以通过如下方式重新进入会话：
1 2 3 4 5 6 7 8 9 10 | -- pdb或非cdb SELECT * FROM DBA_DATAPUMP_SESSIONS; SELECT * FROM DBA_DATAPUMP_JOBS; -- cdb查询 SELECT * FROM CDB_DATAPUMP_SESSIONS; SELECT * FROM CDB_DATAPUMP_JOBS; expdp \'/ AS SYSDBA\' ATTACH=SYS_EXPORT_FULL_01 kill_job |
这里的SYS_EXPORT_FULL_01就是DBA_DATAPUMP_JOBS查询出来的JOB名称。
总SQL语句
这里，麦老师给出自己常用的一个SQL语句，可以查询expdp和impdp的相关会话的详细信息，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | SET LINE 9999 COL OWNER_NAME FOR A10 COL JOB_NAME FOR A25 COL OPERATION FOR A10 COL JOB_MODE FOR A10 COL STATE FOR A15 COL OSUSER FOR A10 COL "DEGREE|ATTACHED|DATAPUMP" FOR A25 COL SESSION_INFO FOR A20 SELECT DS.INST_ID, DJ.OWNER_NAME, DJ.JOB_NAME, TRIM(DJ.OPERATION) OPERATION, TRIM(DJ.JOB_MODE) JOB_MODE, DJ.STATE, DJ.DEGREE || ',' || DJ.ATTACHED_SESSIONS || ',' ||DJ.DATAPUMP_SESSIONS "DEGREE|ATTACHED|DATAPUMP", DS.SESSION_TYPE, S.OSUSER , (SELECT S.SID || ',' || S.SERIAL# || ',' || P.SPID FROM GV$PROCESS P WHERE S.PADDR = P.ADDR AND S.INST_ID = P.INST_ID) SESSION_INFO FROM DBA_DATAPUMP_JOBS DJ -- GV$DATAPUMP_JOB FULL OUTER JOIN DBA_DATAPUMP_SESSIONS DS -- GV$DATAPUMP_SESSION ON (DJ.JOB_NAME = DS.JOB_NAME AND DJ.OWNER_NAME = DS.OWNER_NAME) LEFT OUTER JOIN GV$SESSION S ON (S.SADDR = DS.SADDR AND DS.INST_ID = S.INST_ID) ORDER BY DJ.OWNER_NAME, DJ.JOB_NAME; |
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 | [oracle@mis ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 28 14:44:39 2021 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SET LINE 9999 SQL> COL OWNER_NAME FOR A10 SQL> COL JOB_NAME FOR A25 COL OPERATION FOR A10 COL JOB_MODE FOR A10 SQL> COL STATE FOR A15 COL OSUSER FOR A10 SQL> COL "DEGREE|ATTACHED|DATAPUMP" FOR A25 SQL> COL SESSION_INFO FOR A20 SQL> SELECT DS.INST_ID, 2 DJ.OWNER_NAME, DJ.JOB_NAME, 4 TRIM(DJ.OPERATION) OPERATION, 5 TRIM(DJ.JOB_MODE) JOB_MODE, DJ.STATE, DJ.DEGREE || ',' || DJ.ATTACHED_SESSIONS || ',' ||DJ.DATAPUMP_SESSIONS "DEGREE|ATTACHED|DATAPUMP", 8 DS.SESSION_TYPE, S.OSUSER , (SELECT S.SID || ',' || S.SERIAL# || ',' || P.SPID 11 FROM GV$PROCESS P WHERE S.PADDR = P.ADDR 13 AND S.INST_ID = P.INST_ID) SESSION_INFO 14 FROM DBA_DATAPUMP_JOBS DJ -- GV$DATAPUMP_JOB 15 FULL OUTER JOIN DBA_DATAPUMP_SESSIONS DS -- GV$DATAPUMP_SESSION 16 ON (DJ.JOB_NAME = DS.JOB_NAME AND DJ.OWNER_NAME = DS.OWNER_NAME) 17 LEFT OUTER JOIN GV$SESSION S 18 ON (S.SADDR = DS.SADDR AND DS.INST_ID = S.INST_ID) 19 ORDER BY DJ.OWNER_NAME, DJ.JOB_NAME; INST_ID OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE|ATTACHED|DATAPUMP SESSION_TYPE OSUSER SESSION_INFO ---------- ---------- ------------------------- ---------- ---------- --------------- ------------------------- -------------- ---------- -------------------- 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 DBMS_DATAPUMP oracle 184,15567,32612 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 EXTERNAL TABLE oracle 407,32291,314 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 234,19089,32620 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 252,9183,32627 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 281,11087,32629 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 334,13055,32631 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 435,16993,32633 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 502,3899,32635 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 561,7397,32637 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 606,14503,32639 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 635,20587,32641 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 655,15133,32643 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 685,19545,32645 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 729,16845,32647 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 755,13039,32649 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 785,18903,32651 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 30,5909,32653 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 56,11657,32655 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 85,14787,32657 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 136,10191,32659 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 154,12319,32661 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 180,11931,32663 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 205,28299,32665 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 235,20435,32667 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 284,11739,32669 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 WORKER oracle 303,6463,32671 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 EXTERNAL TABLE oracle 383,4911,312 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 EXTERNAL TABLE oracle 436,7345,316 1 SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 24,1,29 MASTER oracle 129,29369,32618 29 rows selected. SQL> drop table SYS_EXPORT_FULL_01 purge; drop table SYS_EXPORT_FULL_01 purge * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> drop table SYS_EXPORT_FULL_01 purge; drop table SYS_EXPORT_FULL_01 purge * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> drop table SYS_EXPORT_FULL_01 purge; drop table SYS_EXPORT_FULL_01 purge * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> alter system kill session '184,15567' immediate; alter system kill session '184,15567' immediate * ERROR at line 1: ORA-00031: session marked for kill SQL> SQL> drop table SYS_EXPORT_FULL_01 purge; drop table SYS_EXPORT_FULL_01 purge * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> alter system kill session '129,29369' immediate; System altered. SQL> drop table SYS_EXPORT_FULL_01 purge; Table dropped. SQL> |
总结
总之，一句话，杀进程，杀会话，drop表。
如何清除 DBA_DATAPUMP_JOBS 视图中的异常数据泵作业? (Doc ID 1626201.1)
适用于:
Oracle Database Cloud Schema Service - 版本 N/A 和更高版本
Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本
Oracle Database Exadata Express Cloud Service - 版本 N/A 和更高版本
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - 版本 N/A 和更高版本
Oracle Database Backup Service - 版本 N/A 和更高版本
本文档所含信息适用于所有平台
目标
如何清除 DBA_DATAPUMP_JOBS 视图中的异常数据泵作业?
解决方案
用于这个例子中的作业:
- 导出作业
- 导出作业
- 导出作业
- 导出作业
第1步. 用 SQL*PLUS 判断在数据库中有哪些数据泵作业
%sqlplus /nolog
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
