Re: query log

On 7 Apr 2006 07:50:08 -0700, stevedhoward_at_gmail.com wrote:

>You could use a logon trigger to selectively trace some sessions. If
>you are using connection pooling software, this may not prove useful.
>If you would like, on the fly, you could also issue something like the
>following from SQL*PLUS...
>
>begin
> for c in ( select sid,serial#
> from v$session
> where paddr in (select addr
> from v$process
> where nvl(background,0) != 1) ) loop
> dbms_system.Set_Ev(c.sid,c.serial#,10046,4,'');
> DBMS_OUTPUT.PUT_LINE('Turned on level 4 tracing for session ' ||
>c.sid || '...' );
> end loop;
>end;
>/
>
>This will trace all sessions currently logged in, but not new ones (you
>can do that as well, if interested). I wouldn't leave it on too long,
>though, as it can get really verbose, depending on the level of trace.
>If you do go this route, (we do every once in a while for debuigging as
>well), make sure your max_dump_file_size is large.
>
>Regards,
>
>Steve

Alter session set max_dump_file_size = unlimited exists.

Apart from that, it is of course much smarter to use the logon trigger and not trace all sessions.

--
Sybrand Bakker, Senior Oracle DBA