How to Trace Oracle User Logins Using Triggers

Enable tracing for a specific user when they log in to the Oracle database.

Example for tracing USER in Oracle
Suppose I want to trace the SCOTT user when they log in to the database.

Its having two ways 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;