With this query (compatible Oracle RAC) using the v$session_longops view, you can view any SQL statement that executes for more than 6 seconds.
select s.inst_id, SQL.SQL_TEXT as "OPERATION", to_char(START_TIME, 'dd/mm/yyyy hh24:mi:ss') Start_Time, to_char(LAST_UPDATE_TIME, 'dd/mm/yyyy hh24:mi:ss') Last_Update_Time, round(TIME_REMAINING/60,1) as "MINUTES_REMAINING", round((SOFAR/TOTALWORK) * 100,2) as PCT_DONE from gv$session s, gv$sqlarea sql, gv$session_longops op where s.sid=op.sid and s.sql_id = sql.sql_id and s.sid = op.sid and s.status = 'ACTIVE' and op.totalwork > op.sofar and s.sid not in (select distinct sid from gv$mystat where rownum < 2) order by 4 desc;
The SET_SESSION_LONGOPS procedure of the DBMS_APPLICATION_INFO package can be used to publish your application information about the progress of long operations.
You can insert and update rows in the v$session_longops view:
DECLARE rindex BINARY_INTEGER; slno BINARY_INTEGER; totalwork number; sofar number; obj BINARY_INTEGER; BEGIN rindex := dbms_application_info.set_session_longops_nohint; sofar := 0; totalwork := 10; WHILE sofar < 10 LOOP -- update obj based on sofar -- perform task on object target sofar := sofar + 1; dbms_application_info.set_session_longops(rindex, slno, "Operation X", obj, 0, sofar, totalwork, "table", "tables"); END LOOP; END;
After instrumentation, v$session_longops view is populated with these informations:
SELECT opname, target_desc, sofar, totalwork, time_remaining, units FROM v$session_longops;
Author: Vincent Fenoll, Oracle DBA Montreal
Compatibility: Oracle 10g, 11g, 12c