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;