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.
Thanks Obaid. Here I have attached some extra lines which prove more precise to identify the user
ReplyDeleteCREATE 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;
/