When a DBA sees a busy or blocked session, the first question is what it is doing. DBMS_APPLICATION_INFO lets your code answer that: it labels the session with a module, an action, and client information, which appear in V$SESSION, in V$SQL, and in monitoring tools.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_application_info.set_module(module_name => '...', action_name => '...');
dbms_application_info.set_action('...');
dbms_application_info.set_client_info('...');
dbms_application_info.read_module(module_out, action_out);
dbms_application_info.read_client_info(client_info_out);Each value is cut to 64 bytes if it is longer.
Label a Session
A fare loader sets its module and first action, moves on to a second action, and adds client information. Then it reads the values back, and V$SESSION shows them for the current session.
Example:
declare
v_module varchar2(64);
v_action varchar2(64);
v_client varchar2(64);
begin
dbms_application_info.set_module(module_name => 'FARE_LOADER',
action_name => 'reading file');
dbms_application_info.set_action('updating fares');
dbms_application_info.set_client_info('run for route 12');
dbms_application_info.read_module(v_module, v_action);
dbms_application_info.read_client_info(v_client);
dbms_output.put_line(v_module || ' / ' || v_action || ' / ' || v_client);
end;
/
select module, action, client_info from v$session where sid = sys_context('USERENV', 'SID');Output:
FARE_LOADER / updating fares / run for route 12 PL/SQL procedure successfully completed. MODULE ACTION CLIENT_INFO ______________ _________________ ___________________ FARE_LOADER updating fares run for route 12
SET_ACTION replaced only the action, so the module stays FARE_LOADER. Anyone looking at V$SESSION now knows which program this session is and which step it is in.
Find SQL by Module
Statements run while a module is set are recorded with that module and action in V$SQL.
Example:
-- SQL run under a module is tagged with it in V$SQL
exec dbms_application_info.set_module('FARE_REPORT', 'monthly totals')
select /* fare report */ count(*) from bookings;
select module, action, executions
from v$sql
where sql_text like 'select /* fare report */%';
exec dbms_application_info.set_module(null, null)Output:
PL/SQL procedure successfully completed.
COUNT(*)
___________
700
MODULE ACTION EXECUTIONS
______________ _________________ _____________
FARE_REPORT monthly totals 1
PL/SQL procedure successfully completed.The report query is tagged FARE_REPORT / monthly totals, so its cost can be found and grouped by program. The last call clears the module and action.
Things to Know
- Set the module when a unit of work starts and the action at each step; clear them when it finishes, because they stay on pooled sessions.
- Reading V$SESSION and V$SQL needs privileges on those views; setting the values needs none.
- Many tools, including Active Session History and SQL Monitor, report time by module and action.
Related Guides
Conclusion
DBMS_APPLICATION_INFO labels a session with module, action, and client information that show up in V$SESSION, V$SQL, and monitoring tools. Set them as your code moves through its work so every session and statement can be traced back to the program and step that ran it.
