Monitor the EXPDP/IMPDP jobs in Oracle

Monitor the Datapump jobs in Oracle for logical backup

Check the status of job

SELECT owner_name, 
job_name, operation, job_mode, state 
FROM dba_datapump_jobs
where state='EXECUTING';

Check the %completion from v$session_longops table

SELECT 
OPNAME,SID,SERIAL#,CONTEXT,SOFAR,TOTALWORK,   ROUND(SOFAR/TOTALWORK*100,2) "PERCENTAGE_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;

Check the job status from dba_datapump_jobs view

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

Check the DBA_RESUMEABLE if its waiting for some resource

col sql_text for a30
col error_msg for a30
select user_id, session_id, status, start_time, suspend_time, sql_text, error_number,
 error_msg
 from dba_resumable;

Check the wait event information which cause job hanged

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;

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.