Monitor Data Pump Progress with V$SESSION_LONGOPS

Monitor Data Pump Progress with V$SESSION_LONGOPS

Purpose

V$SESSION_LONGOPS shows how far a running expdp or impdp job has got. That matters on a full-database export scheduled overnight, or an import that has been running for six hours. The dynamic performance view gives a number to plan around instead of a guess.

Oracle documents it as a view of "various operations that run for longer than 6 seconds (in absolute time)." Data Pump export and import jobs that transfer table data maintain an entry in it. Per Oracle, the entry reports job progress in megabytes: SOFAR is the megabytes transferred so far, TOTALWORK is the estimated size, and UNITS is MB. Dividing SOFAR by TOTALWORK gives the percentage complete. The OPNAME column holds the job name.

This post covers three queries: a read against V$SESSION_LONGOPS matched to DBA_DATAPUMP_JOBS by job name, a join to V$SESSION for session context, and a read of DBA_DATAPUMP_JOBS for job state, mode and degree. All three queries are read-only.

Code

 1-- Query 1: Active Data Pump jobs with SOFAR/TOTALWORK percentage
 2--          OPNAME holds the job name, so match it to DBA_DATAPUMP_JOBS
 3SELECT sl.sid,
 4       sl.serial#,
 5       sl.username,
 6       sl.opname,
 7       sl.target_desc,
 8       sl.sofar,
 9       sl.totalwork,
10       ROUND(sl.sofar / NULLIF(sl.totalwork, 0) * 100, 1) AS pct_done,
11       sl.units,
12       sl.elapsed_seconds,
13       sl.time_remaining,
14       sl.start_time,
15       sl.last_update_time
16FROM   v$session_longops sl
17WHERE  sl.opname IN (SELECT job_name FROM dba_datapump_jobs)
18  AND  sl.totalwork > 0
19  AND  sl.sofar     < sl.totalwork
20ORDER  BY sl.start_time;
21
22-- Query 2: Join to V$SESSION for module, program, and session status
23SELECT sl.sid,
24       sl.serial#,
25       sl.username,
26       s.module,
27       s.program,
28       s.status,
29       sl.opname,
30       sl.sofar,
31       sl.totalwork,
32       ROUND(sl.sofar / NULLIF(sl.totalwork, 0) * 100, 1) AS pct_done,
33       sl.elapsed_seconds,
34       sl.time_remaining
35FROM   v$session_longops sl
36       JOIN v$session s
37         ON s.sid     = sl.sid
38        AND s.serial#  = sl.serial#
39WHERE  sl.opname IN (SELECT job_name FROM dba_datapump_jobs)
40  AND  sl.totalwork > 0
41ORDER  BY sl.start_time;
42
43-- Query 3: Named Data Pump jobs from DBA_DATAPUMP_JOBS
44--          Cross-reference: V$SESSION_LONGOPS.OPNAME = DBA_DATAPUMP_JOBS.JOB_NAME
45SELECT j.owner_name,
46       j.job_name,
47       j.operation,
48       j.job_mode,
49       j.state,
50       j.degree,
51       j.attached_sessions
52FROM   dba_datapump_jobs j
53WHERE  j.state IN ('EXECUTING', 'NOT RUNNING')
54ORDER  BY j.owner_name, j.job_name;

Code Breakdown

Query 1: the base progress check

This query reads V$SESSION_LONGOPS and keeps only rows that belong to a Data Pump job and are still in progress.

  • opname IN (SELECT job_name FROM dba_datapump_jobs) — Oracle's Data Pump overview says OPNAME holds the job name. The job name can be user-supplied or a server default such as SYS_IMPORT_FULL_03, so a text pattern on the name is unreliable. Matching against DBA_DATAPUMP_JOBS works for either. Without a filter, the view returns every long-running operation Oracle tracks, such as backup and recovery functions, statistics gathering, and query execution.
  • totalwork > 0 — keeps rows that carry a work estimate. Oracle notes that the estimate for exports depends on the type of estimation requested, and that it is updated if the actual transfer exceeds it. The estimate for imports is exact.
  • sofar < totalwork — filters out rows where the transferred amount has reached the estimate, so only unfinished work remains.

The derived column ROUND(sl.sofar / NULLIF(sl.totalwork, 0) * 100, 1) AS pct_done is the headline output: the percentage complete, to one decimal place. NULLIF(sl.totalwork, 0) turns a zero TOTALWORK into NULL, which prevents a divide-by-zero error.

Additional columns worth reading:

  • elapsed_seconds — the number of elapsed seconds from the start of the operation.
  • time_remaining — Oracle's estimate, in seconds, of the time remaining for the operation to complete. It is a projection, not a guarantee.
  • last_update_time — the time statistics were last updated for the operation. A value that has not advanced for several minutes is a reason to look for a blocked session.
  • target_desc — for Data Pump rows, Oracle describes this column as the job operation.
  • units — for Data Pump rows, Oracle documents the unit as megabytes (MB).

Query 2: joining to V$SESSION

V$SESSION_LONGOPS already has its own USERNAME column, which Oracle describes as the user performing the operation. The join to V$SESSION adds session attributes the progress view does not carry.

  • s.module and s.program — the module name set through DBMS_APPLICATION_INFO.SET_MODULE, and the operating system program name. Oracle does not document what Data Pump puts in MODULE, so treat these as context to read, not as a key to join on.
  • s.status — whether the session is ACTIVE, INACTIVE or in another state.
  • sl.serial# in the join condition — required alongside SID because SERIAL# guarantees that session-level commands apply to the correct session if the session ends and another begins with the same session ID.

Oracle states that when several sessions cooperate in one long-running operation, SID corresponds to the main session.

Query 3: DBA_DATAPUMP_JOBS

DBA_DATAPUMP_JOBS carries job metadata that V$SESSION_LONGOPS does not: the job name, the operation type, the job mode, the number of worker processes performing the operation (DEGREE), and the number of sessions attached to the job (ATTACHED_SESSIONS). Oracle's DBMS_DATAPUMP.OPEN valid operation types are EXPORT, IMPORT, and SQL_FILE. Job modes include FULL, SCHEMA, and TABLE.

Filtering on state IN ('EXECUTING', 'NOT RUNNING') returns running jobs and jobs that exist with no active execution. The view also shows control job tables that are not currently associated with an active job, so a row in this view is not proof that a job is executing. Compare STATE with ATTACHED_SESSIONS and with the progress rows from Query 1.

To link a progress row to a job, match V$SESSION_LONGOPS.OPNAME to DBA_DATAPUMP_JOBS.JOB_NAME, as Queries 1 and 2 do. To list the sessions attached to a job, use DBA_DATAPUMP_SESSIONS, whose SADDR column Oracle says "can be used with V$SESSION view."

Key Points

  • Read UNITS with the numbers. For Data Pump table data, Oracle documents SOFAR, TOTALWORK and UNITS as megabytes. Other operations in the view use other units, so check the column before you trust a percentage.
  • The export estimate can move. Oracle says the usefulness of the export estimate depends on the type of estimation requested, and that it is updated if the actual transfer amount exceeds it. Parameters such as COMPRESSION, ENCRYPTION, QUERY and REMAP_DATA are not reflected in the estimate. Import estimates are exact.
  • time_remaining is an estimate. Treat one reading as a hint and watch the trend over several readings.
  • Expect one entry per job operation. Oracle documents an entry that tracks job progress, and says SID corresponds to the main session when several sessions cooperate. Do not expect a separate row for each PARALLEL worker, and do not add up SOFAR across rows.
  • last_update_time is the stall indicator. If this column is not advancing, the job may not be making progress. Check the wait events of the job's sessions in V$SESSION before you conclude it has hung.
  • A state of NOT RUNNING in DBA_DATAPUMP_JOBS is not proof of failure. The view shows jobs "regardless of their state" and control job tables with no active job. Check ATTACHED_SESSIONS and the progress rows in Query 1 to see whether work is still advancing.

Insights and Best Practices

Run the query before reattaching to a detached job

Pressing Ctrl+C during an expdp run stops logging to the terminal and opens the Export prompt, where the job keeps running. The EXIT_CLIENT command "stops the export client session, exits Export, and discontinues logging to the terminal, but leaves the current job running." To reconnect later, use the ATTACH= parameter, for example expdp hr ATTACH=hr.export_job. Before you reattach, run Query 1 to see whether the job is still advancing. A stalled last_update_time is a reason to check wait events first, because attaching to a blocked job does not clear the block.

Use time_remaining to decide whether to intervene

An import showing eight hours of time_remaining at 3 AM with a business-hours deadline at 8 AM demands a different response than one showing thirty minutes remaining. V$SESSION_LONGOPS provides that estimate while the job is live, so the decision — add parallelism, extend the maintenance window, or let it finish — rests on data. Oracle's interactive PARALLEL command can change the number of active worker processes for the current job, and the PARALLEL parameter is valid only in Enterprise Edition.

Monitor last_update_time for stall detection

A lightweight check that queries last_update_time every five minutes and alerts when it has not advanced for fifteen minutes can flag a job that has stopped moving, with no extra monitoring product. These intervals are a starting point to tune, not an Oracle figure. Pair the check with time_remaining and elapsed_seconds before you act on it.

Read the job as one entry, not a sum of workers

Oracle describes one progress entry per Data Pump job operation, in megabytes. Read SOFAR and TOTALWORK from that entry as the job-level view. Adding SOFAR across rows would mix different operations.

Use the interactive STATUS command as a second view

V$SESSION_LONGOPS is not the only way to see progress. The interactive STATUS command "displays cumulative status of the job, a description of the current operation, and an estimated completion percentage." Use it when you are already attached to the job, and use the query when you are not.

Set PARALLEL deliberately and verify the degree

DBA_DATAPUMP_JOBS.DEGREE shows the number of worker processes performing the operation. Compare it with the PARALLEL value you set. Confirming the actual degree early avoids discovering mid-run that the job is using fewer workers than you expected.

When to Use This

  • Checking live progress on a running expdp or impdp without attaching to it.
  • Estimating time remaining on an overnight export before the business day begins.
  • Confirming that a Data Pump job running without an attached client is still making forward progress.
  • Diagnosing a suspected stall by checking last_update_time, then looking at the wait state of the job's sessions in V$SESSION.
  • Reporting completion percentage to operations staff during a database migration where progress updates are needed at regular intervals.
  • Verifying that a parallel export is using the expected number of worker processes, through DBA_DATAPUMP_JOBS.DEGREE.

Troubleshooting Common Issues

If the query returns no rows for an expdp or impdp known to be running, first check that the job appears in DBA_DATAPUMP_JOBS and read its JOB_NAME. Then run SELECT opname, sofar, totalwork, units FROM v$session_longops WHERE opname = '<job_name>' with that name. The view lists only operations that run longer than 6 seconds, so a job that has just started may not have a row yet. Oracle also notes that the export estimate depends on the type of estimation requested, so a row can exist with a rough TOTALWORK.

If pct_done shows 100 but the job has not completed, the estimate may have been reached before the job finished. Oracle says the export estimate is updated if the actual transfer amount exceeds it, so re-read the row after a few minutes. Drop the sofar < totalwork filter briefly if you need to see rows that have reached their estimate.

If time_remaining shifts, watch the trend over several readings rather than acting on a single value. It is Oracle's estimate, and the job-level estimate itself can change.

For rows missing on a multitenant database, confirm which container you are connected to. Oracle's documents do not state how Data Pump job entries behave across containers, so test it on your own system.

References

Posts in this series