Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> logon trigger start 10046 trace
FYI:
Assignment: write a database logon trigger than will start a detailed
oracle trace (10046) to help capture and debug a particular program.
I had to re-create this recently. Thought I could find it quickly via google ( I had at previous job ) ... so here it is for possible usage by others.
DANGER WILL ROBINSON: of course the trace files created can be huge and you don't want to do this type of thing unless you really need to. Do not play around with this unless you understand what it is doing.
create table schema_owner.dba_logon_info (the_user varchar2(30), the_sid varchar2(30), the_serial varchar2(30), the_machine varchar2(64), the_osuser varchar2(30), the_program varchar2(48), the_date date) tablespace where_you_want_it;
drop table schema_owner.dba_logon_info;
create or replace trigger
logon_trace_10046
AFTER LOGON ON DATABASE
DECLARE
v_sysdate date;
v_audsid NUMBER;
CURSOR get_sid IS
SELECT sid, serial#, osuser, machine, program from v$session where audsid=v_audsid;
v_sid_rec get_sid%ROWTYPE; v_sid NUMBER; v_serial NUMBER; v_osuser VARCHAR2(30); v_machine VARCHAR2(64); v_program VARCHAR2(48); v_user VARCHAR2(30);
BEGIN
v_sysdate := sysdate; v_audsid := sys_context('USERENV','SESSIONID'); v_user := user;
OPEN get_sid;
FETCH get_sid INTO v_sid_rec;
if get_sid%FOUND THEN
v_sid := v_sid_rec.sid; v_serial := v_sid_rec.serial#; v_osuser := v_sid_rec.osuser; v_machine := v_sid_rec.machine; v_program := v_sid_rec.program;
values(v_user,v_sid,v_serial,v_machine,v_osuser,v_program,sysdate);
--
if ( v_user = 'WHATEVER' AND v_osuser = 'blahblah' AND ( instr(v_program,'program_name') > 0 ) ) then
sys.dbms_system.set_ev(v_sid,v_serial,10046,12,''); end if;
END;
/
Received on Fri Apr 15 2005 - 08:43:55 CDT