Monitor Data Pump Progress with V$SESSION_LONGOPS

calendar Jul 5, 2026 / · 10 min read · Oracle DBA V$SESSION_LONGOPS Data Pump expdp impdp Oracle Monitoring Dynamic Performance Views Oracle Administration  ·
Share on: linkedin copy

Monitor Data Pump Progress with V$SESSION_LONGOPS

Purpose

Where the expdp and impdp log files record a status line per completed object — writing output at each table boundary, not while a large table is mid-transfer — V$SESSION_LONGOPS records progress while the operation is still running. That gap matters on a full-database export scheduled overnight or an import that has been running for six hours with no new log output. The log file will not tell you how far along the current table is. V$SESSION_LONGOPS will.

Oracle populates this dynamic performance view with progress rows for any operation the database estimates will take more than six seconds. Data Pump worker sessions register their own rows, updated continuously, carrying the amount of work done (SOFAR), the total amount estimated (TOTALWORK), and the units those numbers are measured in. Dividing SOFAR by TOTALWORK gives the percentage complete — a number the log file does not expose in real time.

This post covers three queries: a direct read against V$SESSION_LONGOPS filtered for Data Pump operations, a join to V$SESSION for session context, and a cross-reference to DBA_DATAPUMP_JOBS to map a progress row back to a named job. All three queries are read-only and safe to run against a live production instance.

Code

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

Code Breakdown

Query 1: the base progress check

This query targets V$SESSION_LONGOPS directly, filtering on three conditions that together return only rows representing an active, in-progress Data Pump operation.

  • opname LIKE '%Data Pump%' — the OPNAME column names the type of long-running operation. Data Pump worker sessions register operations whose name contains "Data Pump". Without this filter the view returns all long-running operations across the instance — backup sessions, direct-path reads, and online index builds included.
  • totalwork > 0 — eliminates rows where Oracle has not yet computed a work estimate. A freshly started session may briefly show a zero TOTALWORK before the estimate is ready.
  • sofar < totalwork — filters out completed operations. Once SOFAR reaches TOTALWORK, the row stays in the view for a period but the work is done. Keeping only rows where SOFAR is still less than TOTALWORK shows what remains in progress.

The derived column ROUND(sofar / NULLIF(totalwork, 0) * 100, 1) AS pct_done is the headline output: the percentage of the operation complete, to one decimal place. NULLIF(totalwork, 0) prevents a divide-by-zero error for the edge case where TOTALWORK is still zero at query time.

Additional columns worth reading:

  • elapsed_seconds — how long the current operation has been running, in seconds from start_time.
  • time_remaining — Oracle's estimate of seconds until completion, derived from the current rate of progress. It is a projection, not a guarantee.
  • last_update_time — the timestamp of the most recent progress update from the worker. A last_update_time that is not advancing for several minutes is a signal the session may be blocked or stalled.
  • target — the object being processed in this row, typically the table name for a table-level operation or the dump file name for a job-level summary row.

Query 2: joining to V$SESSION

The join to V$SESSION adds attributes that V$SESSION_LONGOPS does not carry on its own.

  • s.username — the Oracle user who started the Data Pump session. On jobs launched by a dedicated DBA account, this identifies who initiated the export or import.
  • s.module — the Oracle session module field. For Data Pump sessions, Oracle writes the job name into this column. The format varies by release but typically reads as Data Pump Worker 01:JOB_NAME or similar. This string is how you cross-reference a specific progress row with its entry in DBA_DATAPUMP_JOBS.
  • sl.serial# in the join condition — required alongside SID because SID values are reused across the lifetime of an instance. Joining on both SID and SERIAL# ensures the match is against the current session, not a previous session that happened to reuse the same SID.

Query 3: DBA_DATAPUMP_JOBS

DBA_DATAPUMP_JOBS carries the structured job metadata that V$SESSION_LONGOPS does not: the job name assigned at launch, the operation type (EXPORT or IMPORT), the job mode (FULL, SCHEMA, TABLE, TABLESPACE), the number of parallel worker processes (DEGREE), and the count of currently attached client sessions (ATTACHED_SESSIONS).

Filtering on state IN ('EXECUTING', 'NOT RUNNING') returns both actively running jobs and jobs that exist but have no attached client — the common state after an expdp client is detached with Ctrl-C and the job continues running in the background.

To link a row from this query to the progress rows in Queries 1 and 2: match DBA_DATAPUMP_JOBS.JOB_NAME against the MODULE column in V$SESSION for the sessions returned by Query 2.

Key Points

  • Rows persist after an operation completes. V$SESSION_LONGOPS is not cleared between runs. The sofar < totalwork filter keeps only in-progress rows; removing it exposes historical operations from the current instance session.
  • SOFAR and TOTALWORK units vary by operation. The UNITS column identifies what is being counted — rows, megabytes, or internal work units. For a table-level export, UNITS is typically rows. Read UNITS alongside the numbers so the percentage is meaningful.
  • time_remaining is an estimate. Oracle derives it from the current throughput rate. A table with uneven row sizes, a mid-job degree change, or I/O congestion all cause the estimate to shift significantly.
  • Multiple rows per job are expected. Each parallel Data Pump worker process registers its own V$SESSION_LONGOPS entry. An export running with PARALLEL=4 will show four rows, one per worker, each reflecting that worker's progress on its assigned slice of the work.
  • last_update_time is the stall indicator. If this column is not advancing, the worker session is not making progress. Join the session's SID to V$SESSION_WAIT to identify the current wait event before concluding the job has hung.
  • A state of NOT RUNNING in DBA_DATAPUMP_JOBS does not mean the job failed. It means no client session is attached. The workers may still be executing; the progress rows in V$SESSION_LONGOPS confirm whether they are.

Insights and Best Practices

Run the query before reattaching to a detached job

When an expdp client is disconnected mid-run with Ctrl-C, the job continues in the background. Before reattaching with the ATTACH= parameter, run Query 1 to confirm the workers are still advancing. A stalled last_update_time is reason to check wait events before reattaching, because connecting to a blocked job confirms the block without resolving it.

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 at any point in the run, while the job is still live, so the decision — add parallelism, extend the maintenance window, or let it finish — is made with actual data rather than from log timestamps.

Monitor last_update_time for stall detection

A lightweight monitoring check that queries last_update_time every five minutes and alerts when it has not advanced for fifteen minutes catches blocked Data Pump sessions without a commercial monitoring platform. Pair the check with time_remaining and elapsed_seconds to distinguish a genuine stall from a slow final pass over a large compressed object, which can produce normal-looking progress updates at a low rate.

Sum across workers for a job-level view

On a parallel job, each worker row shows its own SOFAR and TOTALWORK. Summing SOFAR across all workers for a given job name gives the total rows processed so far across the whole export; summing TOTALWORK gives the combined estimate. The per-worker breakdown also reveals whether work is distributed evenly — a worker whose SOFAR is significantly behind the others may have drawn a disproportionately large segment or be blocked on a specific object.

Do not rely solely on the log file for large tables

The expdp and impdp log files write one status line when a table finishes. For a schema export dominated by one or two large fact tables, the log may show no new output for hours while the worker is mid-table. V$SESSION_LONGOPS is the only location that shows sub-table progress. Check it early in the run to validate that SOFAR is advancing at a rate consistent with the maintenance window, and check it again before deciding a job has stalled.

Set PARALLEL deliberately and verify the degree

DBA_DATAPUMP_JOBS.DEGREE shows the number of active worker processes. If PARALLEL was set in the expdp command but DEGREE is lower, some workers may have failed to start — perhaps due to resource limits or insufficient undo. Confirming the actual degree early avoids discovering mid-run that the job is running single-threaded when four-way parallelism was expected.

When to Use This

  • Checking live progress on a running expdp or impdp, particularly when the log file has not written a new line for an extended period.
  • Estimating time remaining on an overnight export before the business day begins.
  • Confirming that a background Data Pump job running without an attached client is still making forward progress.
  • Diagnosing a suspected stall by checking last_update_time and joining to V$SESSION_WAIT for the current wait event.
  • 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.

Troubleshooting Common Issues

If the query returns no rows for an expdp or impdp known to be running, inspect the actual OPNAME values in the view with SELECT DISTINCT opname FROM v$session_longops WHERE opname IS NOT NULL. Oracle's exact string varies across major releases — some use "Data Pump Worker", others expose the underlying operation type rather than the Data Pump label. Adjust the LIKE pattern accordingly, or drop the filter temporarily to see all long-running operations and identify the Data Pump rows by matching their SID values against sessions in V$SESSION whose MODULE contains the job name.

If pct_done shows 100 but the job has not completed, the row represents a finished phase or a finished table rather than the overall job. Drop the sofar < totalwork filter briefly to see all rows including completed ones, and look for a job-level summary row where target contains the dump file name rather than a table name.

If time_remaining shifts erratically, the job has moved from a set of small, uniform tables to a large table with a different row-size profile. Erratic estimates are normal in this situation. Watch the trend over several readings rather than acting on a single value.

For rows missing from DBA_DATAPUMP_JOBS on a multitenant instance, confirm that the query is running in the correct PDB. Data Pump jobs and their data dictionary entries are local to the PDB in which the job was created — running the query from the CDB root or a different PDB will not return them.

References

Posts in this series