Monday, February 3, 2014

Datapump cleanup-- dba_datapump_jobs, DBMS_DATAPUMP

When you terminate a datapump job,  the reflection of it can remain on the database..
I mean its work tables and even some datapump worker session can remain..
These reflection can bring extra cost on the database , and constitute a dirty environment..

So to be to clean up the mess, following actions can be taken;

Find the remaining datapump jobs if they still exists with the following query;

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

User following to drop the master tables

DROP TABLE OWNER.JOB_NAME purge;   (take OWNER and JOB_NAME from the output of query above)
Okay, now execute the same query once again;

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

If the jobs are still listed in the output, use following to clear them;

--Again job_name and owner is from query above)

DECLARE
jobtostop NUMBER;
BEGIN
job1 := DBMS_DATAPUMP.ATTACH(JOB_NAME,OWNER);
DBMS_DATAPUMP.STOP_JOB (jobtostop);
END;
/

No comments :

Post a Comment