RE: trace via logon trigger

  • From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 13 May 2004 12:29:49 -0500

->I don't like to use a wider scope than necessary to solve a problem,
I agree, in my case, it was necessary. Our app has many weblogic servers
running in dozen or so unix servers connecting to different userids. I
built my script so that we can turn on tracing in any of those
userids,IP address combination and so original script has many more
lines than posted here. We can't statically turn on tracing from a
script since weblogic can open new connections.

It is also easier to turn off tracing by dropping one trigger and
restarting the app.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfgang Breitling
Sent: Thursday, May 13, 2004 9:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: trace via logon trigger


I don't like to use a wider scope than necessary to solve a problem,
i.e. 
use a "logon on database" trigger when a "logon on schema" is
sufficient. 
Limit potential damage to the smallest possible exposure.

This works for me:

SQL> create user kpayton identified by kpayton;

User created.

SQL> create or replace trigger kpayton.logon_trigger
   2  after logon on kpayton.schema
   3  begin
   4    execute immediate 'alter session set events ''10046 trace name 
context forever, level 4''';
   5   exception
   6    when others then null;
   7  end;
   8  /

Trigger created.

SQL> show errors
No errors.
SQL>


PS. Just because it works outside the trigger doesn't mean it will work 
inside the trigger. User kpayton needs the "alter session" privilege 
granted explicitly and directly.

At 07:58 AM 5/13/2004, you wrote:
>This should work...
>create or replace trigger set_system_event
>after logon  on database
>declare
>v_user dba_users.username%TYPE:=user;
>sql_stmt1 varchar2(256) :='alter session set events '||chr(39)||'10046 
>trace name context forever, level 12'||ch r(39);
>begin
>   if (v_user='XXXX') THEN
>       execute immediate sql_stmt1;
>   end if;
>end;
>/

regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: