Oracle EXPDP/IMPDP (DATAPUMP) Monitoring Scripts

To start with some of the important tables/views that you should refer to monitor Data Pump Jobs are:

• DBA_DATAPUMP_JOBS
• DBA_DATAPUMP_SESSIONS
• DBA_RESUMABLE
• V$SESSION_LONGOPS
• V$SESSION
• V$DATAPUMP_JOB

Script to find status of work done:

select x.job_name,ddj.state,ddj.job_mode,ddj.degree

, x.owner_name,z.sql_text, p.message

, p.totalwork, p.sofar

, round((p.sofar/p.totalwork)*100,2) done

, p.time_remaining

from dba_datapump_jobs ddj

left join dba_datapump_sessions x on (x.job_name = ddj.job_name)

left join v$session y on (y.saddr = x.saddr)

left join v$sql z on (y.sql_id = z.sql_id)

left join v$session_longops p ON (p.sql_id = y.sql_id)

WHERE y.module='Data Pump Worker'

AND p.time_remaining > 0;

Another simple script using only longops view:

select
round(sofar/totalwork*100,2) percent_completed,
v$session_longops.*
from
v$session_longops
where
sofar <> totalwork
order by
target, sid;

Procedure to find the status of job in terms of percentage & number of rows:

SET SERVEROUTPUT ON
DECLARE
ind NUMBER;
h1 NUMBER;
percent_done NUMBER;
job_state VARCHAR2(30);
js ku$_JobStatus;
ws ku$_WorkerStatusList;
sts ku$_Status;
BEGIN
h1 := DBMS_DATAPUMP.attach('&JOB_NAME', '&JOB_OWNER');
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip, 0, job_state, sts);
js := sts.job_status;
ws := js.worker_status_list;
dbms_output.put_line('** Job percent done = ' ||
to_char(js.percent_done));
dbms_output.put_line('restarts - '||js.restart_count);
ind := ws.first;
while ind is not null loop
dbms_output.put_line('rows completed - '||ws(ind).completed_rows);
ind := ws.next(ind);
end loop;
DBMS_DATAPUMP.detach(h1);
end;
/

This package will need JOB_NAME and JOB_OWNER as input parameter. You can fetch this

information from your export/import log or you can use the previous SQL script to get this

information.

Remember that if you are doing expdp/impdp by SYSDBA then execute this package using the

same SYSDBA privilege.