Cacher is the code snippet organizer for pro developers

/********************************************************************************
	AUTO_TRACE_TRIGGER.SQL
  Create trigger that trace session (usefull for cnx pools)

	alter session set events='10046 trace name context forever, level 8';
	alter session set max_dump_file_size = unlimited ;
	alter session set timed_statistics = true;
	alter session set sql_trace=true;

	col TRACEFILE for A100
	SELECT sid, tracefile, s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid
	     FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
	     order by 1 
	    --WHERE audsid = USERENV ('SESSIONID')
	    WHERE sid = 11
	/

********************************************************************************/
define dba=DBA1

-- Create table
create table &dba..AUTOTRACE
(
  USERNAME VARCHAR2(90) DEFAULT '*' not NULL ,
  TRACE    VARCHAR2(3) default 'N' not null,
  OSUSER   VARCHAR2(90) default '*' not null,
  MACHINE  VARCHAR2(192) default '*' not null,
  PROGRAM  VARCHAR2(48) default '*' not null,
  MODULE   VARCHAR2(144) default '*' not null,
  ACTION   VARCHAR2(96) default '*' not null,
  constraint PK_AUTROTRACE primary key (USERNAME, OSUSER, MACHINE, PROGRAM, MODULE, ACTION) 
) 
organization index ;
-- Grant/Revoke object privileges 
grant select on &dba..AUTOTRACE to PUBLIC;
exec DBMS_STATS.GATHER_TABLE_STATS ( ownname => '&dba', tabname => 'AUTOTRACE', estimate_percent => 5, cascade => true );


insert into &dba..AUTOTRACE (USERNAME,TRACE) values  ('SAPR3','Y'); 
insert into &dba..AUTOTRACE (PROGRAM,TRACE) values  ('w3wp.exe','Y'); 


Schema SYS :

create or replace procedure sys.auto_trace is
   cursor c1(p_user_name varchar2,
             p_osuser    varchar2,
             p_machine   varchar2,
             p_info      varchar2) is
      select 1
        from &dba..autotrace
       where username = p_user_name
         and (machine = '*' or machine = p_machine)
         and (osuser  = '*' or osuser  = p_osuser)
         and (module  = '*' or module  = p_info)
       and trace = 'Y';

   l1 number(1);
begin
   open c1(sys_context('USERENV','SESSION_USER'),
           sys_context('USERENV','OS_USER'),
           sys_context('USERENV','HOST'),
           sys_context('USERENV','CLIENT_INFO'));
   fetch c1 into l1;
   if (l1 = 1) then
      /*
      execute immediate 'alter session set events=''10046 trace name context forever, level 8''';
      */
      execute immediate 'alter session set sql_trace=true';
      execute immediate 'alter session set max_dump_file_size = unlimited';
      execute immediate 'alter session set timed_statistics = true';
   end if;
   close c1;
end;
/


create or replace trigger SYS.sys_logon
after logon
on database
begin
--CALL logon_proc;
   auto_trace;
end;
/