SELECT sid, serial#, opname, target_desc, percent, sofar, totalwork, to_char(start_time, 'hh24:mi:ss') start_time, to_char(efin, 'hh24:mi:ss') estimate_fin, case when sofar <> totalwork and last_update_time < sysdate-1/10000 then '*' else null end broken FROM (SELECT sid, serial#, opname, target_desc, sofar, totalwork, to_char(CASE WHEN totalwork = 0 THEN 1 ELSE sofar / totalwork END *100, '990') percent, start_time, last_update_time, start_time +((elapsed_seconds + time_remaining) / 86400) efin FROM v$session_longops ORDER BY CASE WHEN sofar = totalwork THEN 1 ELSE 0 END, efin DESC) WHERE sofar <> totalwork or rownum <= 20;It lists all currently running operations and up to twenty lately finished. The most important columns are estimate_finish – it predicts time of process end, and broken – if contains star (*) it is very possible that process was terminated or hung. Constant 1/10000 (about 8 seconds) in comparison
efin < sysdate-1/10000is to avoid false alerts. If single step of yours process takes more then 8 seconds than you should alter this value to meet your needs. You can find more information on v$session_longops view in Oracle documentation.
Lets take a look how to write your own entries to long operations view. As a samle I'll just use simple loop that for each record in ALL_OBJECTS process will sleep for 0.1 second:
declare v_rindex pls_integer; v_slno pls_integer; begin v_rindex := dbms_application_info.set_session_longops_nohint; for r_qry in (select t.*, rownum rn, count(*) over () cnt from ALL_OBJECTS t ) loop dbms_application_info.set_session_longops ( rindex => v_rindex, slno => v_slno, op_name => 'ALL_OBJECTS processing', target => 0, target_desc => 'ALL_OBJECTS', context => 0, sofar => r_qry.rn, totalwork => r_qry.cnt, units => 'loops' ); dbms_lock.sleep(0.1); end loop; end;So what is important here:
- rindex – for first call it must be ste to dbms_application_info.set_session_longops_nohint – it means add new row to v$session_longops view
- rownum is used to get row number; it is passed to sofar parameter
- analytical function (count(*) over () cnt) is used to calculate all rows (steps) in that process; it is passed to totalwork parameter
- rindex, slno – should be assigned always to the same variable; it is needed to pass information about row that should be changed