Oracle DBAs - Scripts

Sumit Kumar

2. How to generate trace based on schema login?


  • Create a logon trigger for the schema

    • CREATE OR REPLACE TRIGGER SYS.set_trace_SCHEMA
    AFTER LOGON ON DATABASE
    WHEN (USER like 'SCHEMA')
    DECLARE
    lcommand varchar(200);
    BEGIN
    EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
    EXECUTE IMMEDIATE 'alter session set tracefile_identifier=SCHEMA_10046';
    EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
    EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
    END set_trace_new;
    /

    OR

    CREATE OR REPLACE TRIGGER SCHEMA.trace_trig
    AFTER LOGON
    ON DATABASE
    DISABLE
    DECLARE
    sqlstr VARCHAR2(200) := 'alter session set tracefile_identifier = ''SCHEMA_Trace''';
    sqlstr2 VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
    sqlstr3 VARCHAR2(200) := 'ALTER SESSION SET statistics_level=ALL';

    BEGIN
    IF (sys_context('USERENV','MODULE') like 'module-name-from-v$session%') THEN
    execute immediate sqlstr;
    execute immediate sqlstr2;
    execute immediate sqlstr3;
    END IF;
    END trace_trig;
    /
  • Enable the trigger

    • alter trigger SYS.set_trace_SCHEMA enable;
    OR
    alter trigger SCHEMA.trace_trig enable;
  • Generate the TKPROF output and check the output file for details of session

    tkprof dbtst14_ora_123289.trc dbtst14_ora_123289.log explain=system/password sys=no sort=prsela,exeela,fchela