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;

 
 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.