stop/kill datapump jobs the right way.

encounter this while working on datapump import and need to stop it. tried killing the process but it keeps coming.

below is the result after doing some google and check oracle metalink.

1. get the list of datapump jobs.

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state
SELECT owner_name, job_name, operation, 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_SESSIONS
———- ——————– ———– ———– ———– —————–
SYS        SYS_IMPORT_FULL_01   IMPORT      FULL        EXECUTING                   0

2. get the owner_name and job_name above and perform the kill.

SET serveroutput on
SET lines 100
DECLARE
h1 NUMBER;
BEGIN
— Format: DBMS_DATAPUMP.ATTACH(‘[job_name]’,'[owner_name]’);
h1 := DBMS_DATAPUMP.ATTACH(‘SYS_IMPORT_FULL_01‘,’SYS‘);
DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

PL/SQL procedure successfully completed.

3. check if the job still exist.

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE ‘BIN$%’
ORDER BY 1,2;

no rows selected

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s