In Oracle Database, V$SESSION_LONGOPS is a dynamic performance view available in every database release.
It provides visibility into the status of certain long-running operations—typically those that take more than 6 seconds to complete. Examples include:
-
Backup and recovery processes
-
Statistics gathering
-
SQL query executions
The set of operations tracked is determined entirely by Oracle—DBAs cannot influence which operations are monitored.
By querying V$SESSION_LONGOPS, you can get progress updates, elapsed time, and estimated completion time for operations Oracle chooses to track.
The following query displays operation details, progress percentage, and remaining time:
SELECT opname,
username,
sql_fulltext,
TO_CHAR(start_time,'DD-MON-YYYY HH24:MI:SS') AS start_time,
(sofar/totalwork)*100 AS "%_complete",
time_remaining,
s.con_id
FROM v$session_longops s
INNER JOIN v$sql sl USING (sql_id)
WHERE time_remaining > 0;