Query to find long running queries or operation. [ Problem - Want to know how long you have to wait until the operation finishes?]

Query to find long running queries or operation.

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.

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