How To Check the Status of Data Pump Master and Worker Processes

dbtut

5 years ago

You can access summary information about the status of Data Pump Master and worker processes with the following query.

  SELECT s.sid,
         s.module,
         s.state,
         s.event    AS event,
         s.seconds_in_wait           AS secs,
         sql.sql_text                AS sql_text
    FROM v$session s JOIN v$sql sql ON sql.sql_id = s.sql_id
   WHERE s.module LIKE 'Data Pump%'
ORDER BY s.module, s.sid;

SELECT s.sid,

s.module,

s.state,

s.event AS event,

s.seconds_in_wait AS secs,

sql.sql_text AS sql_text

FROM v$session s JOIN v$sql sql ON sql.sql_id = s.sql_id

WHERE s.module LIKE 'Data Pump%'

ORDER BY s.module, s.sid;

You can also find out which object the data pump is dealing with and how long this process will take.

SELECT x.job_name,
       b.state,
       b.job_mode,
       b.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  b
       LEFT JOIN dba_datapump_sessions x ON (x.job_name = b.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;

SELECT x.job_name,

b.state,

b.job_mode,

b.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 b

LEFT JOIN dba_datapump_sessions x ON (x.job_name = b.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;