Many times we trace user sessions and processes on live, but sometimes we need to trace all time or for periods of time, all connections of a scheme. The solution could be to create a trigger “AFTER LOGON ON DATABASE”, but in large environments is costly (disk space, cpu usage)and can be almost impossible. Oracle provides a solution with “AFTER LOGON ON SCHEMA” event.
In the example below we can to trace all connections from SIEBEL user, for example during the weekend, without losing any trace. The code in this case was created from user SYS with this permits to avoid grant to the user SIEBEL the CREATE TRIGGER privilege. If running on RAC traces are in DUMP_DEST or on corresponding location if we use diagnostic_dest.
---- TRACE FOR SIEBEL SCHEMA --- CREATE OR REPLACE TRIGGER SIEBEL.TRACE_ON AFTER LOGON ON SIEBEL.schema BEGIN EXECUTE IMMEDIATE 'alter session set statistics_level=ALL'; EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED'; EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12'''; END TRACE_ON; /
HTH – Antonio NAVARRO