Queries to Monitor Expdp Datapump Jobs Status

July 23, 2018
This post is all about monitoring Data Pump jobs, checking expdp or impdp status, to kill running jobs, troubleshoot hung jobs etc.  In this post you will get different queries using which you can start, stop, resume, kill and see the status of data pump jobs.
When the export or import job is in progress, you can press +C keys to get to the respective datapump  prompt or you can attach to the running job and then issue the STATUS command:
monitor-datapump-status
To monitor executing jobs using dba_datapump_jobs view:

set linesize 200
set pagesize 200
col owner_name format a12
col job_name format a20
col operation format a12
col job_mode format a20
SELECT 
owner_name, 
job_name, 
operation, 
job_mode, 
state 
FROM 
dba_datapump_jobs
where 
state='EXECUTING';
To get the detail information like SID, Serial#, and % of completion:

SELECT 
OPNAME, 
SID, 
SERIAL#, 
CONTEXT, 
SOFAR, 
TOTALWORK,
    ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM 
V$SESSION_LONGOPS
WHERE 
OPNAME in
(
select 
d.job_name
from 
v$session s, 
v$process p, 
dba_datapump_sessions d
where 
p.addr=s.paddr 
and 
s.saddr=d.saddr
)
AND 
OPNAME NOT LIKE '%aggregate%'
AND 
TOTALWORK != 0
AND 
SOFAR <> TOTALWORK;
To check the waiting status and wait event of the job waiting for:
SELECT   w.sid, w.event, w.seconds_in_wait
   FROM   V$SESSION s, DBA_DATAPUMP_SESSIONS d, V$SESSION_WAIT w
    WHERE   s.saddr = d.saddr AND s.sid = w.sid;
To check event event and wait class for a particular SID:
SQL> select COMMAND,STATE,WAIT_CLASS,EVENT,SECONDS_IN_WAIT from v$session where sid=7248 and SERIAL#=56639;

To monitor and perform various operations from the expdp/impdp prompt:

[oracle@orahow ~]$ expdp attach=Job_name
expdp / as sysdba attach=job_name
export>status
export>stop_job
export>start_jop
export>kill_job
To check the orphaned datapump jobs. For orphaned jobs the state will be NOT RUNNING.

SET lines 140
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12 
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;
To check the alert log and query the DBA_RESUMABLE view.
select name, sql_text, error_msg from dba_resumable;

To kill the datapump jobs:

alter system kill session 'SID,SERIAL#' immediate;

That’s all…if you have any suggestions or any other queries to monitor datapump jobs status, please comment us. We will definitely go through it and will include in this article.

Must Read: