Oracle Database / SQL Performance & Memory
Use V$SESSION_LONGOPS without assuming every query reports progress
Some database operations publish work estimates that help support teams describe progress. Other expensive operations do not appear in this view.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT sid, serial#, opname, sofar, totalwork, units, elapsed_seconds, time_remaining FROM v$session_longops WHERE totalwork > 0 AND sofar < totalwork;Read the result
Progress and remaining-time values are estimates for instrumented operations, not a guarantee of completion time or full transaction progress.
Resolve and verify
Match the operation to the actual session and sample whether work increases. Combine it with waits and resource metrics before deciding that a job is stuck.
Operational precautions
Do not cancel a progressing recovery or maintenance operation solely because its initial estimate changed.