Tag Archives: trace user with help of trigger

Trace for user login in Oracle by sql trace

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
BEGIN
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''';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

For disable or removing trigger

Drop trigger logon_trigger;