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

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