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:

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;