Data Pump

Review active Data Pump jobs, their operation, state and parallel degree.

SELECT
  owner_name,
  job_name,
  operation,
  job_mode,
  state,
  degree,
  attached_sessions,
  datapump_sessions
FROM dba_datapump_jobs
ORDER BY owner_name, job_name;

Focus on export jobs when investigating source-side Data Pump activity.

SELECT
  owner_name,
  job_name,
  job_mode,
  state,
  degree,
  attached_sessions
FROM dba_datapump_jobs
WHERE operation = 'EXPORT'
ORDER BY owner_name, job_name;

Focus on import jobs when investigating target-side Data Pump activity.

SELECT
  owner_name,
  job_name,
  job_mode,
  state,
  degree,
  attached_sessions
FROM dba_datapump_jobs
WHERE operation = 'IMPORT'
ORDER BY owner_name, job_name;

Identify the sessions attached to a Data Pump job and the role each session plays.

SELECT
  owner_name,
  job_name,
  instance_id,
  saddr,
  session_type
FROM dba_datapump_sessions
ORDER BY owner_name, job_name, session_type;

Correlate Data Pump session addresses with V$SESSION to review session state and activity.

SELECT
  d.owner_name,
  d.job_name,
  d.session_type,
  s.sid,
  s.serial#,
  s.status,
  s.event,
  s.wait_class
FROM dba_datapump_sessions d
JOIN v$session s
  ON s.saddr = d.saddr
ORDER BY d.owner_name, d.job_name, d.session_type, s.sid;

Review Data Pump table-transfer progress reported through V$SESSION_LONGOPS.

SELECT
  username,
  opname,
  target_desc,
  sofar,
  totalwork,
  units,
  message
FROM v$session_longops
WHERE opname LIKE 'SYS_EXPORT%'
   OR opname LIKE 'SYS_IMPORT%'
ORDER BY start_time DESC;