v$session_longops gives feedback on long running queries.
select
-- lop.sid,
-- lop.serial#,
ses.osuser,
lop.username,
lop.opname,
lop.target,
lop.target_desc,
lop.sofar,
lop.totalwork,
lop.time_remaining estimated_time_remaining_sec,
round( (sysdate - lop.start_time ) * 24*60*60) started_s_ago,
round( (sysdate - lop.last_update_time) * 24*60*60) updated_s_ago,
lop.elapsed_seconds,
lop.message,
sql.sql_text
from
v$session_longops lop left join
v$session ses on lop.sid = ses.sid and
lop.serial# = ses.serial# left join
v$sqlarea sql on lop.sql_id = sql.sql_id
-- where sid = …
order by
lop.start_time desc;
procedure long_proc
create table f(g number);
create or replace procedure long_proc as
rindex pls_integer := dbms_application_info.set_session_longops_nohint;
slno pls_integer;
-- Name of task
op_name varchar2(64) := 'long_proc';
target pls_integer := 0; -- ie. The object being worked on
context pls_integer; -- Any info
sofar number; -- how far proceeded
totalwork number := 1000000; -- finished when sofar=totalwork
-- desc of target
target_desc varchar2(32) := 'A long running procedure';
units varchar2(32) := 'inserts'; -- unit of sofar and totalwork
begin
dbms_application_info.set_module('long_proc',null);
dbms_application_info.set_session_longops (
rindex,
slno);
for sofar in 0..totalwork loop
insert into f values (sofar);
if mod(sofar,1000) = 0 then
dbms_application_info.set_session_longops (
rindex,
slno,
op_name,
target,
context,
sofar,
totalwork,
target_desc,
units);
end if;
end loop;
end long_proc;
/
While the procedure long_proc is runnning, one can issue the following query to get feedback on its progress:
select
time_remaining,
sofar,
elapsed_seconds
from
v$session_longops l,
v$session s
where
l.sid = s.sid and
l.serial# = s.serial# and
s.module ='long_proc'