Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> logon trigger start 10046 trace

logon trigger start 10046 trace

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 15 Apr 2005 06:43:55 -0700
Message-ID: <1113572635.260174.166420@g14g2000cwa.googlegroups.com>


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;

   end if;
   CLOSE get_sid;
   insert into schema_owner.dba_logon_info (the_user, the_sid, the_serial, the_machine, the_osuser, the_program, the_date)

   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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US