Track Long Operations in Oracle

You may need to run a process on 1 million rows and the whole process takes a few hours to finish. In Oracle, there is a way that you can track the process.

Here's a piece of example code in PL/SQL, it takes advantage of package DBMS_APPLICATION_INFO.

 declare
   -- main variables
   ...

   -- long op info
   v_rindex     PLS_INTEGER;
   v_slno       PLS_INTEGER;
   v_totalwork  NUMBER;
   v_sofar      NUMBER;
   v_obj        PLS_INTEGER;

   v_op_name    varchar(100) := 'My work';
   v_units      varchar(100) := 'rows processed';
 begin
   -- Calculate total work (number of rows to be processed, etc.)
   select count(*) into v_totalwork from ...;

   v_sofar := 0;
   v_rindex     := DBMS_APPLICATION_INFO.set_session_longops_nohint;

   for ... -- A loop to process your work
   loop
     ...  -- do your work here.

     -- log longops view.
     v_sofar := v_sofar + 1;
     if mod( v_sofar, 500 ) = 0 then   -- log your operation every 500 rounds
        DBMS_APPLICATION_INFO.set_session_longops(rindex   => v_rindex,
                                               slno        => v_slno,
                                               op_name     => v_op_name,
                                               target      => v_obj,
                                               context     => 0,
                                               sofar       => v_sofar,
                                               totalwork   => v_totalwork,
                                               target_desc => 'Some description here',
                                               units       => v_units);
     end if;

   end loop;

   -- mark the end
   DBMS_APPLICATION_INFO.set_session_longops(rindex   => v_rindex,
                                          slno        => v_slno,
                                          op_name     => v_op_name,
                                          target      => v_obj,
                                          context     => 0,
                                          sofar       => v_sofar,
                                          totalwork   => v_totalwork,
                                          target_desc => 'Some description here',
                                          units       => v_units);

 end;
 /

To view the status of the process, run query:

 select sid, serial#, opname, sofar, totalwork, start_time, last_update_time
 from v$session_longops where opname = 'My Work';

Columns sofar and totalwork show you how much work has been done so far.