Oracle DataPump崩溃了，现在无法创建新工作

汤姆，

一项为期一个月的原定任务突然坠毁。
将数据传输到外部SSD (USB3.0) 的EXPDP作业。

磁盘仍然运行。

要导出的新测试后出现的错误如下 :( EXPDP SYSTEM/XXXX @ ....)
ORA-31626: 现在作业存在
ORA-31638: 无法附加用户系统的作业SYS_EXPORT_SCHEMA_01
ORA-06512: 在 “SYS.DBMS_SYS_ERROR”，第95行
......

我阅读了一些提示，导致删除所有DataPump作业 (我认为太多了)。

来自MOS Doc ID 336014.1

Step 1. Determine in SQL*Plus which Data Pump jobs exist in the database:

%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

-- locate Data Pump jobs:

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
Step 2. Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be 'NOT RUNNING'.

Step 3. Check with the job owner that the job with status 'NOT RUNNING' in dba_datapump_jobs is not an export/import Data Pump job that has been temporary stopped, but is actually a job that failed. (E.g. the full database export job by SYSTEM is not a job that failed, but was deliberately paused with STOP_JOB).

Step 4. Identify orphan DataPump external tables. To do this, run the following as SYSDBA in SQL*Plus:

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
/

Correlate the information from DBA_OBJECTS and DBA_EXTERNAL TABLES above to identify the temporary external tables that belong to the DataPump orphaned jobs.
Drop the temporary external tables that belong to the DataPump orphaned job. eg:

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


Step 5. Determine in SQL*Plus the related master tables:

-- locate Data Pump master tables:

COL owner.object FORMAT a50

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;


Step 6. For jobs that were stopped in the past and won't be restarted anymore, delete the master table. E.g.:

DROP TABLE .sys_export_table_02;

-- For systems with recycle bin additionally run:
purge dba_recyclebin;
Note:
=====
Following statement can be used to generate the drop table statement for the master 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$%';
 

NOTE:
In case the table name is mixed case, you can get errors on the drop, e.g.:
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
  

Because the table has a mixed case, try using these statements with double quotes around the table name, for instance:
drop table SYSTEM."impdp_SCHEMA_TEST_04102015_1";
drop table SYSTEM."impdp_schema_TEST_10202014_0";
  

Step 7. Re-run the query on dba_datapump_jobs and dba_objects (step 1 and 4). If there are still jobs listed in dba_datapump_jobs, and these jobs do not have a master table anymore, cleanup the job while connected as the job owner. E.g.:

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;
/

Note that after the call to the STOP_JOB procedure, it may take some time for the job to be removed. Query the view user_datapump_jobs to check whether the job has been removed:

CONNECT /

SELECT * FROM user_datapump_jobs;


Step 8. Confirm that the job has been removed:

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

-- locate Data Pump jobs: 

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

-- locate Data Pump master tables:

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