Skip to content

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.

[Post Views: 792]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

2 Comments

  1. santosh santosh

    Great article. Also include queries using which we can see the wait events for which expdp datapump jobs is waiting for.

  2. Erman Erman

    Thanks

Leave a Reply

Required fields are marked *