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