Tracing A Schema By Using Triggers

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s