在Oracle中，如何彻底停止expdp或impdp进程？

许多同事在使用expdp或impdp命令时，不小心按了CTRL+C组合键，然后又输入exit命令（或者网络中断等异常现象），导致expdp或impdp进程不存在，但Oracle数据库的会话仍存在，所以dmp文件也一直在增长（或数据一直在导入到数据库中）。

处理过程

在这种情况下的处理办法如下所示：

1、检查expdp进程是否还在

若存在，则可用kill -9 process命令杀掉expdp或impdp的进程。

2、杀会话、删表

检查会话是否仍存在，若存在则把相关的会话杀掉（注意：先使用命令“ALTER SYSTEM KILL SESSION '22,33' immediate;”在数据库级别杀掉会话，然后在OS级别使用kill -9杀掉进程），如无杀会话的权限则可以将相关的表DROP掉，表名可以使用如下的SQL来查询：

例如：

使用相同的办法也删除从视图DBA_DATAPUMP_JOBS中查询出来的表，直到2个视图无记录。

3、删除导出的dmp文件。

如不删除，则重新执行expdp命令时，会报dmp文件已存在。

使用kill_job停止

如果没有退出expdp或impdp会话，则可以输入kill_job来直接停止导出导入进程也是可以的。

若是已经退出会话，则也可以通过如下方式重新进入会话：

这里的SYS_EXPORT_FULL_01就是DBA_DATAPUMP_JOBS查询出来的JOB名称。

总SQL语句

这里，麦老师给出自己常用的一个SQL语句，可以查询expdp和impdp的相关会话的详细信息，如下所示：

示例：

总结

总之，一句话，杀进程，杀会话，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 视图中的异常数据泵作业?

解决方案

用于这个例子中的作业:
- 导出作业 .EXPDP_20051121 是一个正在运行的 schema 级别的导出作业
- 导出作业 .SYS_EXPORT_TABLE_01 是一个表级别的异常导出作业
- 导出作业 .SYS_EXPORT_TABLE_02 是一个表级别的停止导出作业
- 导出作业 .SYS_EXPORT_FULL_01 是一个被暂停的全库导出作业

第1步. 用 SQL*PLUS 判断在数据库中有哪些数据泵作业

%sqlplus /nolog

CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
COL owner.object for a50

-- 查找数据泵作业:

SELECT owner_name, job_name, rtrim(operation) "OPERATION",
rtrim(job_mode) "JOB_MODE", state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED

EXPDP_20051121 EXPORT SCHEMA EXECUTING 1
SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0
SYS_EXPORT_TABLE_02 EXPORT TABLE NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0

第2步. 确保在 dba_datapump_jobs 中列出的作业不是活动的数据泵作业: 状态应该是'NOT RUNNING'。

第3步. 同作业属主确认视图 dba_datapump_jobs 中状态为'NOT RUNNING' 的作业不是被暂停，而失败的作业。(例如，SYSTEM 用户的全库导出作业不是一个失败的作业，而是一个被故意暂停的作业)

第4步. 使用SYSDBA在SQL*Plus中执行下面的命令来确认DataPump的外部表:

set linesize 200 trimspool on
set pagesize 2000
col owner form a30
col created form a25
col last_ddl_time form a25
col object_name form a30
col object_type form a25
select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from dba_objects
where object_name like 'ET$%'
/

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
order by 1,2
/

联合DBA_OBJECTS和DBA_EXTERNAL来确认属于这个失败的作业所关联的外部表
并且删除它

SQL> drop table system.&1 purge;
Enter value for 1: ET$00654E1E0001
old 1: drop table system.&1 purge
new 1: drop table system.ET$00654E1E0001 purge

第5步. 通过 SQL*Plus 找到相关的 Primary 表：

-- 查找数据泵的 Primary 表：

SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------ -------------------------
VALID 85283 TABLE .EXPDP_20051121
VALID 85215 TABLE .SYS_EXPORT_TABLE_02
VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01

select table_name, owner from dba_external_tables;

第6步. 对于过去被终止的和根本不会再启动的作业，删除它的 Primary 表，例如，

DROP TABLE .sys_export_table_02;

-- 对于启用了 recycle bin 的系统，需要额外运行：
purge dba_recyclebin;

注意:
=====
下面的语句可以用来生成 drop primary table 的语句:

SELECT 'DROP TABLE '||o.owner||'.'||object_name||' PURGE;'
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%';

**注意:**
如果表名是大小写混合的，删除时可能会遇到如下错误：

SQL> drop table SYSTEM.impdp_schema_TEST_10202014_0;
drop table SYSTEM.impdp_schema_TEST_10202014_0
*
ERROR at line 1:
ORA-00942: table or view does not exist

因为表名是大小写混合，所以删除时要用双引号括起来，例如：

drop table SYSTEM."impdp_SCHEMA_TEST_04102015_1";
drop table SYSTEM."impdp_schema_TEST_10202014_0";

第7步. 重新运行第1步和第4步对 dba_datapump_jobs 和 dba_objects 的查询。如果 dba_datapump_jobs 里仍然有作业列出，并且这些作业根本没有 Primary 表，我们就可以以作业属主的身份清除它们。例如，

CONNECT /

SET serveroutput on
SET lines 100
DECLARE
h1 NUMBER;
BEGIN
h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','');
DBMS_DATAPUMP.STOP_JOB (h1);
END;
/

注意：调用 STOP_JOB 过程以后，可能会花一点时间去清除作业，我们可以查询 user_datapump_jobs 检查作业是否已经被清除掉：

CONNECT /

SELECT * FROM user_datapump_jobs;

第8步. 确认作业已经被清除

CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
COL owner.object for a50

-- 查找数据泵作业:

SELECT owner_name, job_name, rtrim(operation) "OPERATION",
rtrim(job_mode) "JOB_MODE", state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED
---------- ------------------- --------- --------- ----------- --------
EXPDP_20051121 EXPORT SCHEMA EXECUTING 1
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0

-- 查找数据泵的 Primary 表:

SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------ -------------------------
VALID 85283 TABLE .EXPDP_20051121
VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01

### 摘要:

1. 异常数据泵作业不会影响新的数据泵作业. dba_datapump_jobs 是基于 gv$datapump_job, obj$, com$, and user$ 的一个视图。 这个视图显示仍在运行的数据泵作业，或者作业的 Primary 表仍然保留在数据库中，或者不正常结束的作业(异常作业)。如果一个新的数据泵作业启动， 会创建一条新的记录，与旧的数据泵作业无关。

2. 当用系统自动生成的作业名启动一个新的数据泵作业时，我们会检查 dba_datapump_job 中现有的名称以保持惟一性。当然，启动这个作业的用户下需要有足够的空间来创建一个新的 Primary 表。

3. 数据泵作业与用 DBMS_JOBS 包定义的作业不同， DBMS_JOBS 创建的作业使用它自己的进程。 数据泵作业使用一个 Primary 进程和一些 worker 进程。如果一个数据泵作业被暂停，数据泵作业会一直存在在数据库中（status: NOT RUNNING），这时，Primary和 worker 进程会被停止，或者不再存在。客户端之后可以再次挂载到这个作业，并且继续作业的执行(START_JOB)。

4. 如果活动的数据泵作业相关联的 Primary 表被删除，可能会导致不一致.

4.a. 如果是一个导出作业, 不太可能引起不一致，因为删除 Primary 表只会导致数据泵的 mater 和 worker 进程中止。这种情况类似于客户端发起的一个意外中止。

4.b. 如果这个作业是一个导入作业，那么情况就有所不同。删除掉 Primary 表会导致数据泵的 worker 和 mater 进程中断。这有可能会引起不完整的导入。 例如，没有导入表的所有数据， 或表，索引，视图等的导入不完整， 这种情况类似于意外中断导入的客户端。

删除 Primary 表本身不会引起任何数据字典的不一致。如果您在作业完成后还保留 Primary 表，以后再删除 Primary
表的操作不会造成任何不一致。

5. 除了 'NOT RUNNING'状态以外，失败的JOB还可能处于'DEFINING'状态，尝试attach到这种job上会报以下的错误：

$ expdp system/ attach=system.sys_export_schema_01

Export: Release 11.2.0.4.0 - Production on Tue Jan 27 10:14:27 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 405
ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user SYSTEM
ORA-31632: Primary table "SYSTEM.SYS_EXPORT_SCHEMA_01" not found, invalid, or inaccessible
ORA-00942: table or view does not exist

清理这种JOB的方法和上面提到的是一样的。