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 saysOPNAMEholds the job name. The job name can be user-supplied or a server default such asSYS_IMPORT_FULL_03, so a text pattern on the name is unreliable. Matching againstDBA_DATAPUMP_JOBSworks 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.moduleands.program— the module name set throughDBMS_APPLICATION_INFO.SET_MODULE, and the operating system program name. Oracle does not document what Data Pump puts inMODULE, so treat these as context to read, not as a key to join on.s.status— whether the session isACTIVE,INACTIVEor in another state.sl.serial#in the join condition — required alongsideSIDbecauseSERIAL#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
UNITSwith the numbers. For Data Pump table data, Oracle documentsSOFAR,TOTALWORKandUNITSas 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,QUERYandREMAP_DATAare not reflected in the estimate. Import estimates are exact. time_remainingis 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
SIDcorresponds to the main session when several sessions cooperate. Do not expect a separate row for eachPARALLELworker, and do not add upSOFARacross rows. last_update_timeis 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 inV$SESSIONbefore you conclude it has hung.- A
stateofNOT RUNNINGinDBA_DATAPUMP_JOBSis not proof of failure. The view shows jobs "regardless of their state" and control job tables with no active job. CheckATTACHED_SESSIONSand 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 inV$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
- V$SESSION_LONGOPS — Oracle Database Reference 19c - Authoritative column reference for the view, covering SOFAR, TOTALWORK, UNITS, TIME_REMAINING, ELAPSED_SECONDS, and LAST_UPDATE_TIME
- DBA_DATAPUMP_JOBS — Oracle Database Reference 19c - Column reference for the Data Pump job catalog view, including STATE, DEGREE, OPERATION, JOB_MODE, and ATTACHED_SESSIONS
- Oracle Data Pump Overview — Database Utilities 19c - Oracle's guide to Data Pump, including the section on monitoring job progress with V$SESSION_LONGOPS and the columns that apply to a job
- Data Pump Export — Database Utilities 19c - Oracle's reference for Ctrl+C, the EXIT_CLIENT, STATUS and PARALLEL interactive commands, and the ATTACH parameter
- DBA_DATAPUMP_SESSIONS — Oracle Database Reference 19c - Sessions attached to a Data Pump job, with the SADDR column that links to V$SESSION
- Oracle Data Pump (expdp/impdp) — oracle-base.com - A worked walkthrough of Data Pump job creation, parallel operation and client attach/detach