Feb 23, 2019

expdp job still executing even after killed from OS level

I killed a expdp job from OS level but I saw that the job still executing. I started the job as background like below:

$ nohup expdp \"/ as sysdba\" parfile=export_emp_tables.par &

When I saw from v$session, I found program as "oracle@example01 (DM00)". That means DMOO background process started to execute the job. i.e., master process DM00 started.

You can find "master process DM00 started" message from alert log also. There it self you can find complete session details.

Then, How we can clear that session?

Step1: Check the job name with schema

Find the Job name.

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 
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_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
SYS        SYS_EXPORT_TABLE_01  EXPORT       TABLE        EXECUTING                    1
SYSTEM     SYS_EXPORT_FULL_01   EXPORT       FULL         NOT RUNNING                  0


Here Running job is 'SYS_EXPORT_TABLE_01'.


Step 2: To stop the job perform below steps

Syntax

exec DBMS_DATAPUMP.STOP_JOB (DBMS_DATAPUMP.ATTACH('JOB_NAME','OWNER',1.0)

‘1’ is to abort the job immediately
‘0’ is to remove the job from the job list so that it is not restartable

SQL> exec DBMS_DATAPUMP.STOP_JOB (DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','SYS'),1,0);

Example:
SQL> exec DBMS_DATAPUMP.STOP_JOB (DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','SYS'),1,0);

PL/SQL procedure successfully completed.
SQL>

Verify again:

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_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
SYSTEM     SYS_EXPORT_FULL_01   EXPORT       FULL         NOT RUNNING                  0

Now it seems, no job is running.

No comments:

Post a Comment

Translate >>