Re: Setting SQL trace on another session

I have always had problems with MAX_DUMP_FILE_SIZE (in regards to tracing someone else's session on Sun Solaris, 8i, 9i and 10g) and have found that the only reliable way to set it (for me) is via the init.ora. Even when I have changed it using the following it seems to remain locked at 5Mb (I did experiment with different values but still couldn't get it working correctly :(

exec sys.dbms_system.set_int_param_in_session(sid, serial#, 'max_dump_file_size', 1000000);

But maybe I was doing something consistently wrong? Also, when you start the trace and it hits a limit you might need to reset it somehow.

I now only use oradebug when tracing someone else's session and it works so much better!

Try these commands from sqlplus
-- Try these commands as I seemed to get a trace working finally, ie, reset the max_dump_file_size

oradebug help

oradebug setospid 10161
oradebug UNLIMIT
oradebug Event 10046 trace name context forever, level 12 oradebug tracefile_name
oradebug Event 10046 trace name context off

oradebug procstat
oradebug close_trace
oradebug flush

GaneshKannan_at_gmail.com wrote:
> Hello all,
> Db : Oracle 9.2.0.7 standard edition
>
> I recently tried to set sql trace session for a session, and had an
> issue with trace file not being big enough.
>
> Steps that I performed are:
>
> 1) Asked the user to connect to db, without start processing
> 2) used exec dbms_system.set_sql_trace_in_session(sid,snum, true);
> to start tracing
> 3) used sys.dbms_system.set_in_param_in_session() to set the
> MAX_DUMP_FILE_SIZE in the session to 10,000,000
> 4) asked user to run the process
> 5) used exec dbms_system.set_sql_trace_in_session(sid,snum, false) to
> stop tracing.
>
> The problem occured was, though I have set the trace file in the
> session to be appx 10 MB (10,000,000), the trace file size was 50,000
> KB, and cannot grow anymore. So trace was incomplete. Also to note is
> INIT.ORA parameter MAX_DUMP_FILE_SIZE is set to 100,000.
>
> My question is
> 1) Did I set the MAX_DUMP_FILE_SIZE parameter properly, if so why did
> it go over 10 MB for the session
>
> 2) Should the user session level MAX_DUMP_FILE_SIZE value be less than
> that of INIT.ORA value.
>
> Any suggestions/comments highly appreciated.
>
> - gK
>
Received on Wed Apr 26 2006 - 18:19:42 CDT