Clear the Isolated Data Pump job in DBA_DATAPUMP_JOBS, dba_datapump_jobs

Clear the Isolated Data Pump job in DBA_DATAPUMP_JOBS, dba_datapump_jobs

Today, when restructuring the database (transferring tables and indexes in the tablespace to other tablespaces), we found two strange objects: SYS_EXPORT_FULL_01 and SYS_EXPORT_FULL_02, this may be an object left behind when an EXPDP export exception is found, but there is not much information found. You cannot confirm its specific purpose and whether the table is deleted. Later, my colleague found How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS In metalink? (Document ID 336014.1) This article. Learn more about these tables.

As shown in the following figure, SYS_EXPORT_FULL_01 and SYS_EXPORT_FULL_02 are complete database export jobs. The status is not running, meaning that the job is temporarily stopped. In fact, if the job fails, it is not running.

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; 

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

This indicates the previous (may be a long time ago) stopped jobs. Of course, these jobs cannot be restarted. You can delete these master tables.

Drop table admin. SYS_EXPORT_FULL_01;

Drop table admin. SYS_EXPORT_FULL_02;

 

How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS? (Documentation ID 336014.1)The details are as follows:

 

The jobs used in this example:
-Export job SCOTT. expdp_2001_21 is a schema level export that is running
-Export job SCOTT. SYS_EXPORT_TABLE_01 is an orphaned table level export job
-Export job SCOTT. SYS_EXPORT_TABLE_02 is a table level export job that was stopped
-Export job SYSTEM. SYS_EXPORT_FULL_01 is a full database export job that is temporary stopped

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
------------------------------------------------------------------
SCOTT expdp_200120021 export schema executing 1
SCOTT SYS_EXPORT_TABLE_01 export table not running 0
SCOTT 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 shocould 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 isNotA job that failed, but was deliberately paused with STOP_JOB ).

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

-- 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 scott. expdp_2002.1621
VALID 85215 table scott. SYS_EXPORT_TABLE_02
VALID 85162 table system. SYS_EXPORT_FULL_01

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

Drop table scott. sys_export_table_02;
-- For systems with recycle bin additionally run:
Purge dba_recyclebin;

NOTE:
In case the table name is mixed case, you can get errors on the drop, e.g .:

SQL> drop table SYSTEM. impdp_schema_STGMDM_10202014_0;
Drop table SYSTEM. impdp_schema_STGMDM_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_STGMDM_04102015_1 ";
Drop table SYSTEM. "impdp_schema_STGMDM_10202014_0 ";

Step 6. 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 scott/tiger
SET serveroutput on
SET lines 100
DECLARE
H1 NUMBER;
BEGIN
H1: = DBMS_DATAPUMP.ATTACH ('sys _ EXPORT_TABLE_01 ', 'Scott ');
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 scott/tiger
SELECT * FROM user_datapump_jobs;

Step 7. 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
------------------------------------------------------------------
SCOTT expdp_200120021 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 scott. expdp_2002.1621
VALID 85162 table system. SYS_EXPORT_FULL_01

Remarks:
1. orphaned Data Pump jobs do not have an impact on new Data Pump jobs. the view dba_datapump_jobs is a view, based on gv $ datapump_job, obj $, com $, and user $. the view shows the Data Pump jobs that are still running, or jobs for which the master table was kept in the database, or in case of an abnormal end of the Data Pump job (the orphaned job ). if a new Data Pump job is started, a new entry will be created, which has no relation to the old Data Pump jobs.

2. when starting the new Data Pump job and using a system generated name, we check the names of existing Data Pump jobs in the dba_datapump_job in order to obtain a unique new system generated jobname. naturally, there needs to be enough free space for the new master table to be created in the schema that started the new Data Pump job.

3. A Data Pump job is not the same as a job that is defined with DBMS_JOBS. jobs created with DBMS_JOBS use there own processes. data Pump jobs use a master process and worker process (es ). in case a Data Pump still is temporary stopped (STOP_JOB while in interactive command mode), the Data Pump job still exists in the database (status: not running ), while the master and worker process (es) are stopped and do not exist anymore. the client can attach to the job at a later time, and continue the job execution (START_JOB ).

4. The possibility of snapshot uption when the master table of an active Data Pump job is deleted, depends on the Data Pump job.
4. a. If the job isExport job, Specified uption is unlikely as the drop of the master table will only cause the Data Pump master and worker processes to abort. This situation is similar to aborting an export of the original export client.
4. B. If the job isImport jobThen the situation is different. when dropping the master table, the Data Pump worker and master processes will abort. this will probably lead to an incomplete import: e.g. not all table data was imported, and/or table was imported incomplete, and indexes, views, etc. are missing. this situation is similar to aborting an import of the original import client.

The drop of the master table itself, does not lead to any data dictionary partition uption. if you keep the master table after the job completes (using the uninitialized ented parameter: KEEP_MASTER = Y), then a drop of the master table afterwards, will not cause any interrupted uption.

5. Instead of the status 'not running' the status of a failed job cocould also be 'defining'. When trying to attach to such a job, this wowould fail:

$ Expdp system/manager 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,201 1, 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 & quot; SYS. KUPV $ FT & quot;, line 405
ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user SYSTEM
ORA-31632: master table "SYSTEM. SYS_EXPORT_SCHEMA_01" not found, invalid, or inaccessible
ORA-00942: table or view does not exist

The steps to cleanup these failed/orphaned jobs are the same as mentioned above.