Datapump jobs

CANCEL, STOP, RESUME or CANCEL

 When you want to kill, stop, resume or cancel a datapump job you must use the command prompt

Hoe identificeer ik een datapump job?

Zoek eerst de jobnaam op via sqlplus: 

SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM     SYS_EXPORT_FULL_01   EXPORT     FULL       EXECUTING 

Hoe stop of kill ik een datapump job die nog draait?

Kill: job wordt definitief verwijderd. log en dumpfiles worden verwijderd
Stop: job kan nog resumed worden

Als je in een commandline zit en je draait export dan zie je de datapump promt

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]

Met <Ctrl>C kan je de dp prompt verlaten. 

Daarna kan je met KILL_JOB commando job killen:

      Export> KILL_JOB

Met STOP_JOB=IMMEDIATE kan je job stoppen :

     Export> STOP_JOB=IMMEDIATE
     Are you sure you wish to stop this job ([yes]/no): yes

Hoe attach ik aan een running job?

Zorg eerst dat je achter de datapump jobnaam komt via SQL commando.

Geef attach commando in de command prompt 

     expdp system ATTACH=EXP_FULL

Je komt dan in de datapump command line en kan een commando geven

> START_JOB(=SKIP_CURRENT): Hiermee skip je current job

of 

CONTINUE_CLIENT :   (restart de idle job)

Als je status van job wil weten doe dan

ctrl+c, STATUS, CONTINUE_CLIENT

Cleanup orphaned datapump jobs

In many cases you sometimes stop data pump job or in case of an abnormal end of the Data Pump job (the orphaned job) or using undocumented parameter KEEP_MASTER=Y, the master table remain in the database. This are orphaned datapump jobs

Algemene info

1) You can check the orphaned data pump from the state column of the view dba_datapump_jobs

and DBA_DATAPUMP_JOBS is based on gv$datapump_job, obj$, com$, and user$.

Orphaned Data Pump jobs do not have an impact on new Data Pump jobs.

 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) For a new data pump job without any job name it is used a system generated name. 

From the dba_datapump_jobs it is checked for existing data pump jobs and then obtain a 

unique new system generated jobname.
3) Data pump jobs are different from DBMS_JOBS and they are maintained
differently. Jobs created with DBMS_JOBS use there own processes. Data Pump
jobs use a master process and worker process(es).
4) If you drop the master table while doing the data pump export or data pump
import operation then the scenario is discussed below.

In case of export if you drop data pump export operation then export process
will abort.
In case of import if you drop data pump import operation then import process
will abort while it leads an incomplete import.

If the data pump job is completed and master table exist (a common if you do
export operation with KEEP_MASTER=y) then it is safe to drop the master table.

Step by step cleanup orphaned datapump jobs

Step 01: Check the orphaned datapump jobs.
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs;


OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        ATTACHED_SESSIONS

---------- -------------------- ---------- ---------- ------------ -----------------

ABCD       SYS_EXPORT_SCHEMA_01 EXPORT     SCHEMA     NOT RUNNING                  0

ABCD       SYS_EXPORT_SCHEMA_02 EXPORT     SCHEMA     NOT RUNNING                  0

Step 02: Check the state field. For orphaned jobs the state will be NOT RUNNING. 

Step 03: Drop the master table.

DROP TABLE ABCD.SYS_EXPORT_SCHEMA_01;
DROP TABLE ABCD.SYS_EXPORT_SCHEMA_02;


Step 04: Check for existing data pump jobs by query issued in step 01.
If objects are in recyclebin bin then purge the objects from the recyclebin.
SELECT owner_name, job_name, operation, job_mode,state, attached_sessions 
FROM dba_datapump_jobs;


OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
ABCD       BIN$xMNQdACzQ6yl22kj EXPORT     SCHEMA     NOT RUNNING                  0
          9U0B8A==$0
ABCD       BIN$BmUy4r5MSX+ojxFk EXPORT     SCHEMA     NOT RUNNING                  0
         sw8ocg==$0



PURGE TABLE ABCD.SYS_EXPORT_SCHEMA_01;
Table purged.

PURGE TABLE ABCD.SYS_EXPORT_SCHEMA_02;
Table purged.

Check if there is any orphaned jobs again. via


SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs;


geeft leeg.
 

Step 05: In this stage you did not get any orphaned jobs if the jobs have a master table.

 If there are still jobs listed in dba_datapump_jobs do cleanup process like below.

SET serveroutput on

SET lines 100

DECLARE

job1 NUMBER;

BEGIN

job1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_01','ABCD');

DBMS_DATAPUMP.STOP_JOB (job1);

END;

/

DECLARE

job2 NUMBER;

BEGIN

job2 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_02','ABCD');

DBMS_DATAPUMP.STOP_JOB (job2);

END;

/