-- 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;
*/