Re: oracle log user activity, from outside db machine?

From: Igor Racic <igor.racicC_at_freesurf.fr_no_C>
Date: Sun, 11 Jul 2004 17:31:59 +0200
Message-ID: <40f15cfe$0$29375$626a14ce@news.free.fr>

It seems he doesn't have performance problem but bug in application. So, level 4 of event 10046 could be enough. Also, I suppose, having several sessions writing in user_dump_dest impact may be much more then neglicible.

If he is on 10g, then FGA may be of help.

Just my 2c

Igor

Sybrand Bakker wrote:
> Comments inline
>
>
> On Sun, 11 Jul 2004 09:10:46 +0200, Bjoern Wiechmann
> <bwiechmann_at_t-online.de> wrote:
>
>

>>hi there,
>>
>>I need to log user activity of a large oracle 9i system under stress. 
>>Solaris based, database size is incredible. (sadly enough: the task is 
>>to log the full sql statements, with all data, of any user - its for 
>>debug purposes of some strange bugs in a distributed multiuser application)
>>Thats why I expect it to happen, to enable audit or sql_trace would 
>>almost "kill" the performance. 

>
> Other than filling up the disk, the impact of sql_trace is neglicible.
> Audit won't help you.
>
>
>
> I have another large machine available
>

>>which could do the logging - if only I knew: Is it possible to use 

>
> something similar to sql_trace, but from outside the db-machine?
> No, why would it
>
> Like a
>

>>"man-in-the-middle" listener tool or something? Or could someone guide 
>>me to a calculation, how much performance down I'll have when enabling 
>>sql_trace on the full machine? Or maybe the "perfect" idea to do this 
>>logging and tracing, somewhere available?
>>Thanks for any informations.
>>
>>Bjoern

>
>
>
> selectively trace sessions by using event 10046 level 12.
> Can be enabled by
> alter session set event '10046 trace name context forever, level 12'
> Can also be set by alter system in a running system.
> Can be disabled by
> alter session set event '10046 trace name context off'
>
> You can also enable individual session
> by using
> begin
> sys.dbms_system.set_ev(sid, serial#,10046,12,'');
> end;
>
> Or
> you can create a database trigger
> running the alter session command for invididual users
>
> create or replace trigger after_logon on database
> begin
> execute immediate '<etc>';
> end;
> /
>
> Or find most resource-consuming statemetns in v$sqlarea.
>
> Etc, etc, etc
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Sun Jul 11 2004 - 10:31:59 CDT