SQL> begin
  2  delete from  gsmcrmdw.wc_loy_txn_f_aa
  3  where  integration_id in
  4  ( select  integration_id
  5    from    support_olap.recover_wc_loy_txn_f_953to955
  6  );
  7  commit;
  8  end;
  9  /

col time_remaining head 'Seconds|Remaining'
col scanned_blocks head 'Scanned Blocks'
col all_blocks head 'All Blocks'
col blocks_remaining head 'Blocks|Remaining'
col opname format a25
col target format a35
col username format a15
col MB_per_Second form 990.0 head 'MB/s'
col pct_scanned head '%Scanned' format 990.00
col predicted_runtime_seconds head 'Estmd.|Runtime|Seconds'
col total_blocks head 'Total|Blocks'
col sid format 99990
col block_size format 99990 head 'Block|Size'
col hash_passes_temp format a20

with
      scan_data as (
                     select
                              to_number(
                                     substr(a.message
                                    ,instr(a.message,': ',1,2)+2
                                    ,instr(a.message,' out of ',1,1)-instr(a.message,': ',1,2)-1
                                    )
                                       ) 
                            / to_number(
                                     substr(a.message
                                    ,instr(a.message,' out of ',1,1)+8
                                    ,instr(a.message,' Blocks done',1,1)-instr(a.message,' out of ',1,1)-7
                                    )
                                       ) *100 pct_scanned
                           ,  to_number(
                                     substr(a.message
                                    ,instr(a.message,' out of ',1,1)+8
                                    ,instr(a.message,' Blocks done',1,1)-instr(a.message,' out of ',1,1)-7
                                    )
                                       )
                            - to_number(
                                     substr(a.message
                                    ,instr(a.message,': ',1,2)+2
                                    ,instr(a.message,' out of ',1,1)-instr(a.message,': ',1,2)-1
                                    )
                                       ) blocks_remaining
                            , a.time_remaining
                            , a.opname
                            , to_number(b.value) block_size
                            , a.target
                            , a.sid
                            , a.username
                     from   (
                              select
                                      replace(v$session_longops.message,'RMAN:','RMAN') message
                                    , v$session_longops.time_remaining
                                    , v$session_longops.opname
                                    , v$session_longops.target
                                    , v$session_longops.sid
                                    , v$session_longops.username
                              from  v$session_longops
                                  , v$session
                              where v$session_longops.sid = v$session.sid
                            ) a
                           ,v$parameter b
                     where a.time_remaining > 0
                     and b.name = 'db_block_size'
                   )
select 
         round(blocks_remaining*block_size/1024/1024/time_remaining,1) MB_per_Second
       , scan_data.time_remaining
       , round(time_remaining/(1-pct_scanned/100)) predicted_runtime_seconds
       , scan_data.pct_scanned
       , scan_data.blocks_remaining
       , round(blocks_remaining/(1-pct_scanned/100)) total_blocks
       , scan_data.opname
       , scan_data.BLOCK_SIZE
       , scan_data.target
       , (
          select
                  DECODE(MAX(NUMBER_PASSES),0,'OPTIMAL',1,'ONE-PASS',NULL,NULL,'MULTI-PASS')||DECODE(max(TEMPSEG_SIZE),NULL,NULL,','||max(TEMPSEG_SIZE))
          from v$sql_workarea_active
          where v$sql_workarea_active.sid = scan_data.sid
          and scan_data.opname in ('Hash Join','Sort Output')
          and OPERATION_TYPE in ('HASH-JOIN','SORT','WINDOW (SORT)')
         ) hash_passes_temp
       , scan_data.sid
       , scan_data.username
from scan_data
order by time_remaining
/

    select  s.sid, s.username, rn.name,     rs.curext
           ,rs.curblk,  t.used_ublk, t.used_urec
    from    v$transaction   t
           ,v$session       s
           ,v$rollname      rn
           ,v$rollstat      rs
    where  t.addr     = s.taddr
    and    t.xidusn   = rn.usn
    and    rn.usn     = rs.usn
order by s.sid
/

create or replace procedure message2log (message IN varchar2)
is
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert into log values (sysdate,message);
  commit;
end;

create or replace trigger log4table
  before delete
  on <table_name>
  for each row
begin
  message2log('deleting '||:old.num);
end;