Oracle DATAPUMP EXPDP/IMPDP Monitoring Scripts

When monitoring EXPDP/IMPDP jobs, we rely on log files generated by the processes and alert logs for error tracking. While this works in most cases, detailed monitoring is crucial for large datasets and long-running sessions. Below are useful queries to help monitor your Data Pump jobs more effectively.

Key Tables/Views to Monitor Data Pump Jobs:

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

Script to Find the Status of Work Done

This query provides information on the progress of the Data Pump job, including percentage completed and time remaining.

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) AS 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 the LONGOPS View

This query provides a simpler overview of the progress, using just the v$session_longops view.

SELECT 
    ROUND(sofar / totalwork * 100, 2) AS percent_completed, 
    v$session_longops.* 
FROM 
    v$session_longops 
WHERE 
    sofar <> totalwork 
ORDER BY
    target, sid;

Procedure to Find Job Status in Terms of Percentage & Number of Rows

This PL/SQL procedure provides the status of the job, including the percentage completed and the number of rows processed.

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;
/

Important Notes:

• The procedure requires JOB_NAME and JOB_OWNER as input parameters. You can retrieve this information from your export/import log or use the previous SQL script to obtain it.
• If you’re performing the expdp/impdp operation as SYSDBA, be sure to execute this package using the same privilege level (SYSDBA).

This method enables more granular and real-time monitoring of long-running expdp/impdp jobs, ensuring you can track progress and troubleshoot effectively.