Search

After logon trigger - trace user sessions

Updated: Jan 28

-- Activate trace for sessions using after logon database trigger

--

-- Tables:

--

-- SYS.XX_LOGIN_TRACE_CONDITION - add here the conditions for activating trace

-- SYS.XX_LOGIN_TRACED_SESSIONS - contains the sessions for which the trace was

activated (with trace file name)

-- SYS.XX_LOGIN_TRACE_ERRORS - errors during execution of logon trigger

--

-- Trigger:

--

-- SYS.XX_LOGIN_TRACE_TRG

--

-- This script should be executed by SYS user

------------------------------------------------------------------------

-- SYS.XX_LOGIN_TRACE... tables

------------------------------------------------------------------------

create table SYS.XX_LOGIN_TRACE_CONDITION

(USERNAME VARCHAR2(30),

OSUSER VARCHAR2(30),

MACHINE VARCHAR2(100),

PROGRAM VARCHAR2(100),

MODULE VARCHAR2(100),

ACTIVE CHAR(1)

);


-- For Oracle Applications add the following grant:

grant select, insert, update, delete on SYS.XX_LOGIN_TRACE_CONDITION to apps with grant option;


create table SYS.XX_LOGIN_TRACED_SESSIONS

( SID NUMBER,

SERIAL# NUMBER,

USERNAME VARCHAR2(30),

OSUSER VARCHAR2(30),

MACHINE VARCHAR2(100),

PROGRAM VARCHAR2(100),

MODULE VARCHAR2(100),

START_DATE DATE,

TRACE_FILE CHAR(300)

);


-- For Oracle Applications add the following grant:

grant select, insert, update, delete on SYS.XX_LOGIN_TRACED_SESSIONS to apps with grant option;


create table SYS.XX_LOGIN_TRACE_ERRORS

(ERROR_TIME DATE,

ERROR_TEXT VARCHAR2(1000)

);


-- For Oracle Applications add the following grant:

grant select, insert, update, delete on SYS.XX_LOGIN_TRACE_ERRORS to apps with grant option;


------------------------------------------------------------------------

-- SYS.XX_LOGIN_TRACE_TRG after logon trigger

------------------------------------------------------------------------

create or replace trigger SYS.XX_LOGIN_TRACE_TRG after logon on database

declare

m_sid v$session.sid%type := 0;

m_serial v$session.serial#%type := 0;

m_sessionid v$session.audsid%type := 0;

m_err varchar2(1000) := '';

begin


if USER in ('SYS', 'SYSTEM', 'DBSNMP')

then

return;

end if;


select SYS_CONTEXT('USERENV','SESSIONID') into m_sessionid from dual;


begin

select s.sid, s.serial#

into m_sid, m_serial

from v$session s,

sys.xx_login_trace_condition c

where s.audsid = m_sessionid

and (upper(s.username) like upper(c.username) or c.username is null)

and (upper(s.osuser) like upper(c.osuser) or c.osuser is null)

and (upper(s.machine) like upper(c.machine) or c.machine is null)

and (upper(s.program) like upper(c.program) or c.program is null)

and (upper(s.module) like upper(c.module) or c.module is null)

and upper(c.active) = 'Y'

and rownum = 1;

exception

when no_data_found then

return;

when others then

m_err := SQLERRM;

insert into SYS.XX_LOGIN_TRACE_ERRORS (error_time, error_text)

values (sysdate, m_err);

return;

end;


begin

execute immediate 'alter session set MAX_DUMP_FILE_SIZE = UNLIMITED';

dbms_system.set_ev (m_sid, m_serial, 10046, 12, '');

exception

when others then

m_err := SQLERRM;

insert into SYS.XX_LOGIN_TRACE_ERRORS (error_time, error_text)

values (sysdate, m_err);

return;

end;


insert into sys.xx_login_traced_sessions (sid, serial#, username, osuser, machine, program, module, start_date, trace_file)

(select s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.module, sysdate, p.tracefile

from v$session s, v$process p

where s.paddr = p.addr

and s.sid = m_sid

and s.serial# = m_serial);


exception

when others then

m_err := SQLERRM;

insert into SYS.XX_LOGIN_TRACE_ERRORS (error_time, error_text)

values (sysdate, m_err);

end;

/


---------------------------------------------------------------------------------------

-- Example of inserts into SYS.XX_LOGIN_TRACE_CONDITION

-- for activating trace on discoverer sessions

---------------------------------------------------------------------------------------

/*

insert into SYS.XX_LOGIN_TRACE_CONDITION (username, osuser, machine, program, module, active)

values ('APPS', '', '', '', 'dis51usr.exe', 'Y');


insert into SYS.XX_LOGIN_TRACE_CONDITION (username, osuser, machine, program, module, active)

values ('APPS', '', '', '', 'dis51adm.exe', 'Y');


insert into SYS.XX_LOGIN_TRACE_CONDITION (username, osuser, machine, program, module, active)

values ('APPS', '', '', '', 'Disco%', 'Y');


commit;

*/

------------------------------------------------------------------------


/*

select * from SYS.XX_LOGIN_TRACE_CONDITION;

select * from SYS.XX_LOGIN_TRACE_ERRORS;

select * from SYS.XX_LOGIN_TRACED_SESSIONS;

*/




39 views0 comments

Recent Posts

See All

Get DDL for Oracle object

set feed off set long 1000000 set longchunksize 1000000 set pages 0 set lines 32767 set trimspool on set feed off set wrap on set serveroutput on format wrapped set def off set tab off exec dbms_metad

Cancelled scheduled concurrent requests

This SQL query returns all concurrent requests that were scheduled in the past and are not scheduled currently. It can be used for identifying scheduled requests that failed to be resubmitted. select

Foreign key constraint violation

/* This PL/SQL script checks violations of foreign key constraints Example of the tables: CREATE TABLE temp_supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_na