Trace for particular user in oracle by trigger
Enable trace for a particular user in oracle when its login/connected to the database.
Example for tracing USER in Oraclee
Suppose I want to trace the SCOTT user when its login to the database.
Its having two way you can enable auditing on user SCOTT by enabling audit_trail=db parameter.
For tracing I am created trigger for it. When user SCOTT connected with the database the “logon event” execute the following trigger which will enable the trace for the session. The trace file is created in default location.
select value from v$diag_info where name ='default trace file';
For creating trigger to trace USER SCOTT
CREATE OR REPLACE TRIGGER logon_trigger
AFTER logon ON DATABASE
IF (USER = 'SCOTT' ) THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER=''SCOTT_APP''';
EXECUTE IMMEDIATE 'alter session set timed_statistics=true';
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
WHEN OTHERS THEN
For disable or removing trigger
Drop trigger logon_trigger;