How to find out Expected Time of Completion for an Oracle Query

The option is using v$session_longops. Below is a sample query that will give you percentage of completion of a running Oracle query and Expected Time to Complete in minutes,

Script

SELECT
opname,
target,
ROUND( ( sofar/totalwork ), 4 ) * 100 Percentage_Complete,
start_time,
CEIL( time_remaining / 60 ) Max_Time_Remaining_In_Min,
FLOOR( elapsed_seconds / 60 ) Time_Spent_In_Min
FROM v$session_longops
WHERE sofar != totalwork;

If you have access to v$sqlarea table, then you can use another version of the above query that will also show you the exact SQL running. Here is how to get it,

SELECT 
opname
target,
ROUND( ( sofar / totalwork ), 4 ) * 100 Percentage_Complete,
start_time,
CEIL( time_remaining / 60 ) Max_Time_Remaining_In_Min,
FLOOR( elapsed_seconds / 60 ) Time_Spent_In_Min,
AR.sql_fulltext,
AR.parsing_schema_name,
AR.module Client_Tool
FROM v$session_longops L, v$sqlarea AR
WHERE L.sql_id = AR.sql_id
AND totalwork > 0
AND AR.users_executing > 0
AND sofar != totalwork;

Note

This query will give you correct result only if a FULL Table Scan or INDEX FAST FULL SCAN are being performed by the database for your query. In case, there is no full table/index fast full scan, you can force Oracle to perform a full table scan by specifying /*+ FULL() */ hint.