Andrew Fraser DBA

10046 trace a module using logon trigger

1) Update – thanks to Joaquin Gonzalez for pointing out a preferable safer method than logon trigger for 10046 tracing a particular module:

EXEC DBMS_MONITOR.serv_mod_act_stat_enable ( service_name => 'myservice' , module_name => 'myModule.exe' , binds => TRUE )

You can verify service_name is what you expect by checking the column of the same name in gv$sql.

To switch that off once you are done:

EXEC DBMS_MONITOR.serv_mod_act_stat_disable ( service_name => 'myservice' , module_name => 'myModule.exe' )

2) To generate 10046 traces just for a particular SQL statement, regardless of when it runs or who runs it:

ALTER SYSTEM SET EVENTS 'sql_trace [sql: sql_id=cnju0jr4y2ujs] bind=true , wait=true' ;

And to switch that off:

ALTER SYSTEM SET EVENTS 'sql_trace off' ;

H/t Julian Dyke.

3) The less preferable option of using a logon trigger to 10046 trace a particular module is:

CREATE OR REPLACE TRIGGER sys.set_trace
AFTER LOGON ON DATABASE
WHEN ( USER != 'SYS' )
BEGIN
   IF SYS_CONTEXT ( 'userenv' , 'module' ) LIKE 'myModule.exe%'
   THEN
      DBMS_MONITOR.session_trace_enable ( binds => TRUE ) ;
   END IF ;
END set_trace ;
/

For versions 10gR2 and below, replace the dbms_monitor line above with the older style syntax:

EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''' ;

Drop the logon trigger once you have collected the information you need, session tracing does add an overhead, especially when recording bind values as above.