Problem
Want to know how long you have to wait until the operation finishes?
Sometimes when you run a slow query which produces a full table scan, you'll have to know how long it's going to take for the query to finish. There's a simple query which shows you how many seconds elapsed and how many seconds you'll have to wait.
Solution
Recipe #1 - Get details about long running operations
Run the following query and you'll get most information you need:
SELECT osuser,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining
FROM v$session_longops sl
inner join v$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL#
WHERE time_remaining > 0
Recipe #2 - Show long running SQL Statements
If you also want to see the SQL query causing the long running operation, just join v$sql like show below:
SELECT s.username,
sl.sid,
sq.executions,
sl.last_update_time,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining,
sq.sql_fulltext
FROM v$session_longops sl
INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0
Comments
Santosh Tiwary
I like this query. Got very detailed information on <a href="https://orahow.com/find-long-running-queries-in-oracle/" rel="follow">long running sessions</a>. SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
Leave a Comment