News

  1. Home
  2. All posts
All posts, PL/SQL by Team Ajara

Logon trigger at DB level for tracing oracle session


Logon trigger at DB level for tracing oracle session.

During performance testing , sometimes we want to trace all the sessions from a user and from a particula server and verify if there any long running sessions
This is to isolate the issue and verify further and get the queries rewritten by Application team.

This kind of excercise helps in finding bad queries before production cutover activity.

——Below code is used to trace the sessions that are connected with DB username with SIEBEL ..

CREATE OR REPLACE TRIGGER logon_SIEBEL
AFTER LOGON ON DATABASE
when (USER=’SIEBEL’ )
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE ‘alter session set sql_trace=true’;
EXECUTE IMMEDIATE ‘alter session set statistics_level=ALL’;
EXECUTE IMMEDIATE ‘alter session set max_dump_file_size=UNLIMITED’;
EXECUTE IMMEDIATE ‘alter session set events ”10046 trace name context forever, level 12”’;
EXECUTE IMMEDIATE ‘alter session set timed_statistics = true’;
EXECUTE IMMEDIATE ‘alter session set tracefile_identifier = ”SIEBEL”’;
END ;/

 

To do  Disable/enable/drop  the trigger.

–alter trigger logon_SIEBEL disable;

–alter trigger logon_SIEBEL enable;

–drop trigger logon_SIEBEL;

——Below code is used to trace the sessions that are connected with DB username with SIEBEL and coming from from the HOST PosOra.

 

CREATE OR REPLACE TRIGGER logon_SIEBEL

AFTER LOGON ON DATABASE

when (USER=’SIEBEL’ and sys_context(‘USERENV’,’HOST’)=’PosOra’ )

DECLARE

lcommand varchar(200);

BEGIN

EXECUTE IMMEDIATE ‘alter session set sql_trace=true’;
EXECUTE IMMEDIATE ‘alter session set statistics_level=ALL’;
EXECUTE IMMEDIATE ‘alter session set max_dump_file_size=UNLIMITED’;
EXECUTE IMMEDIATE ‘alter session set events ”10046 trace name context forever, level 12”’;
EXECUTE IMMEDIATE ‘alter session set timed_statistics = true’;
EXECUTE IMMEDIATE ‘alter session set tracefile_identifier = ”SIEBEL”’;

END ;

/

sys_context:

The purpose of the Oracle SYS_CONTEXT function is to return information about the Oracle environment that you’re working on.
Here sys_context function returns hostname of the session , if host is PosOra then only that user sessions will be traced and creates tracefiles

 

See Also

Trigger AFTER STARTUP ON DATABASE

LOGON Trigger In the Schema level to set sort area

http://oracle.com