DBMS_APPLICATION_INFO is an Oracle package that helps developers log the names of running modules or transactions in the database. This information is useful for monitoring performance and debugging.

Key Features

Registering Applications

When you register an application with the database, system administrators and performance tuning experts can monitor its performance by module. The application’s name and actions are saved in the V$SESSION and V$SQLAREA views.

Security Model

You don’t need extra permissions to use the _DBMS_APPLICATION_INFO_ package. The _DBMSAPIN.SQL_ script runs automatically when the database is created.

Operational Notes

Applications should automatically set the name of the module and action whenever a user enters that module. The module name can be the name of a form in an Oracle Forms application or the name of the code section in an Oracle Precompilers application. The action name usually describes what the current transaction is within that module.

Subprograms

SET_MODULE Procedure

This procedure sets the name of the current application or module. It also sets the initial action. For example:

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

This registers the program as a module and shows that it is trying to add records to the “order” table.

SET_ACTION Procedure

This procedure names the current action being performed in the module. For example:

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

This reflects the current status or action of the session

SET_CLIENT_INFO Procedure

This procedure supplies additional information about the client application. For example:

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;
/

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;