DBMS_APPLICATION_INFO

The DBMS_APPLICATION_INFO package allows programs to add information to the V$SESSION and V$SESSION_LONGOPS views to make tracking of session activities more accurate.

Once the program initiates it registers itself using the SET_MODULE procedure. In doing so it also sets the initial action:

BEGIN
  DBMS_APPLICATION_INFO.set_module(module_name => 'add_order',
                                   action_name => 'insert into orders');
  
  -- Do insert into ORDERS table.
END;
/

Subsequent processing can use the SET_ACTION procedure to make sure the action description stays relevant:
Assuming that the "fireid" user is to be audtited:

BEGIN
  DBMS_APPLICATION_INFO.set_action(action_name => 'insert into order_lines');
  
  -- Do insert into ORDER_LINES table.
END;
/

The SET_CLIENT_INFO procedure can be used if any additional information is needed:

BEGIN
  DBMS_APPLICATION_INFO.set_action(action_name => 'insert into orders');
  DBMS_APPLICATION_INFO.set_client_info(client_info => 'Issued by Web Client');
  
  -- Do insert into ORDERS table.
END;
/

The information set by these procedures can be read from the V$SESSION view as follows:

SET LINESIZE 500
SELECT sid,
       serial#,
       username,
       osuser,
       module,
       action,
       client_info
FROM   v$session;

The SET_SESSION_LONGOPS procedure can be used to show the progress of long operations by inserting rows in the V$SESSION_LONGOPS view:

DECLARE
  v_rindex     PLS_INTEGER;
  v_slno       PLS_INTEGER;
  v_totalwork  NUMBER;
  v_sofar      NUMBER;
  v_obj        PLS_INTEGER;
BEGIN
  v_rindex     := DBMS_APPLICATION_INFO.set_session_longops_nohint;
  v_sofar     := 0;
  v_totalwork := 10;

  WHILE v_sofar < 10 LOOP
    -- Do some work
    DBMS_LOCK.sleep(5);

    v_sofar := v_sofar + 1;
    DBMS_APPLICATION_INFO.set_session_longops(rindex      => v_rindex, 
                                              slno        => v_slno,
                                              op_name     => 'Batch Load', 
                                              target      => v_obj, 
                                              context     => 0, 
                                              sofar       => v_sofar, 
                                              totalwork   => v_totalwork, 
                                              target_desc => 'BATCH_LOAD_TABLE', 
                                              units       => 'rows processed');
  END LOOP;
END;
/

The information in the V$SESSION_LONGOPS view can be queried using:

SELECT  opname,
        target_desc,
        sofar,
        totalwork,
        units
FROM    v$session_longops;

For more information see:

DBMS_APPLICATION_INFO

Hope this helps. Regards Tim...

Back to the Top.