Andrew Fraser DBA

Logon Trigger set Module

Ideally developers will set module and action to allow for easy debugging and performance monitoring in v$session, v$active_session_history, dba_hist_sqlstat, OEM screens, etc.

If not, a logon trigger can be used to set these. Below does this for any tomcat sessions which still have module set to the default generic value. Pattern matching is used for meaningful machine (docker) names, a lookup table for more obscure machine-module mappings:

CREATE OR REPLACE TRIGGER myOwner.set_module
AFTER LOGON ON DATABASE
WHEN ( SYS_CONTEXT ( 'userenv' , 'module' ) = 'JDBC Thin Client' AND SYS_CONTEXT ( 'userenv' , 'os_user' ) = 'tomcat' )
BEGIN
   myOwner.set_module_proc ;
END ;
/
CREATE OR REPLACE PROCEDURE myOwner.set_module_proc 
AS
   l_module_name VARCHAR2(64) ;
   l_machine VARCHAR2(64) := SYS_CONTEXT ( 'userenv' , 'host' ) ;
BEGIN
   IF l_machine LIKE 'un%'
   THEN
      SELECT MAX ( s.module ) INTO l_module_name
        FROM myOwner.module_lookup_table s
       WHERE s.machine = l_machine
      ;
   ELSIF l_machine LIKE '%-digital%'
   THEN
      l_module_name := SUBSTR ( l_machine , 1 , INSTR ( l_machine , '-digital' , 1 , 1 ) - 1 ) ;
   ELSIF l_machine LIKE '%-n0%'
   THEN
      l_module_name := SUBSTR ( l_machine , 1 , INSTR ( l_machine , '-n0' , 1 , 1 ) - 1 ) ;
   END IF ;
   IF l_module_name IS NOT NULL
   THEN
      dbms_application_info.set_module ( module_name => l_module_name , action_name => NULL ) ;
   END IF ;
END set_module_proc ;
/

Caveat emptor: logon triggers that go invalid will prevent new logons (other than / as sysdba) so test thoroughly before deploying.