Oracle: V$SESSION_LONGOPS

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;

select
   lpad(' ', 2*depth) || pln.operation                       opr,
   pln.options                                               opt,
   round(100/nullif(to_number(slo.target), 0)*slo.sofar)     pct,
   slo.target                                                tgt,
   slo.sofar,
   slo.totalwork,
   slo.units,
   round((sysdate - slo.start_time       ) * 24 * 60) sta_m_ago,
   round((sysdate - slo.last_update_time ) * 24 * 60) upd_m_ago,
   slo.message,
   ses.state,
   ses.status  
-- pln.*,
-- slo.*
from
   v$session         ses                                                     left join
   v$sql_plan        pln  on ses.sql_address    = pln.address          and 
                             ses.sql_hash_value = pln.hash_value             left join
   v$session_longops slo  on pln.address        = slo.sql_address      and
                             pln.hash_value     = slo.sql_hash_value   and
                             pln.id             = slo.sql_plan_line_id and
                             ses.sid            = slo.sid              and
                             ses.serial#        = slo.serial#
where
   ses.sid = ‥
order by
   pln.id;

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;
/

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'