Oracle EXPDP/IMPDP (DATAPUMP) Monitoring Scripts

Usually we monitor the EXPDP/IMPDP jobs by monitoring the log files generated by expdp/impdp process. Also we monitor alert log too just in case some error pops up. This helps most of the time. If you have a long running expdp/impdp sessions as you are exporting/importing huge GBs then it helps to have a more detailed monitoring of the expdp/impdp jobs. Some of the useful queries which can be used to monitor the Data Pump Jobs are mentioned below.

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.

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Oracle EXPDP/IMPDP (DATAPUMP) Monitoring Scripts

Related posts:

2 Comments