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.<\/p>\n
Once the program initiates it registers itself using the SET_MODULE procedure. In doing so it also sets the initial action:<\/p>\n
BEGIN
DBMS_APPLICATION_INFO.set_module(module_name => ‘add_order’,
action_name => ‘insert into orders’);<\/p>\n
— Do insert into ORDERS table.
END;
\/<\/p>\n
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:<\/p>\n
BEGIN
DBMS_APPLICATION_INFO.set_action(action_name => ‘insert into order_lines’);<\/p>\n
— Do insert into ORDER_LINES table.
END;
\/<\/p>\n
The SET_CLIENT_INFO procedure can be used if any additional information is needed:<\/p>\n
BEGIN
DBMS_APPLICATION_INFO.set_action(action_name => ‘insert into orders’);
DBMS_APPLICATION_INFO.set_client_info(client_info => ‘Issued by Web Client’);<\/p>\n
— Do insert into ORDERS table.
END;
\/<\/p>\n
The information set by these procedures can be read from the V$SESSION view as follows:<\/p>\n
SET LINESIZE 500
SELECT sid,
serial#,
username,
osuser,
module,
action,
client_info
FROM v$session;<\/p>\n
The SET_SESSION_LONGOPS procedure can be used to show the progress of long operations by inserting rows in the V$SESSION_LONGOPS view:<\/p>\n
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;<\/p>\n
WHILE v_sofar < 10 LOOP
— Do some work
DBMS_LOCK.sleep(5);<\/p>\n
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;
\/<\/p>\n
The information in the V$SESSION_LONGOPS view can be queried using:<\/p>\n
SELECT opname,
target_desc,
sofar,
totalwork,
units
FROM v$session_longops;<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue lendo