Saturday, July 2, 2011

User Trace

In order to trace all the actions by a specific user we can user below log on trigger:

CREATE OR REPLACE TRIGGER myuser_logon_trace
AFTER LOGON ON MYUSER.SCHEMA
BEGIN
execute immediate 'alter session set tracefile_identifier="MYUSER"';
DBMS_SESSION.SET_SQL_TRACE(TRUE);
END;
/

Now, we can find the output of the trace at "user_dump_dest" with "MYUSER" in the name.

1 comment:

  1. Thanks Obaid. Here I have attached some extra lines which prove more precise to identify the user


    CREATE OR REPLACE TRIGGER MYUSER_logon_trace
    AFTER LOGON ON MYUSER.SCHEMA
    declare
    sesid number;
    vname varchar2(40);
    BEGIN
    select sid into sesid from v$mystat where rownum = 1;
    vname := 'MYUSER_'||sesid;
    execute immediate 'alter session set tracefile_identifier='||vname;
    DBMS_SESSION.SET_SQL_TRACE(TRUE);
    END;
    /

    ReplyDelete