Re: Setting SQL trace on another session

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

Well you set it to 100,000 .. That is not 100,000 bytes. Its 100,000 in operating system blocks. In your case that is 512 bytes. So max dump file size is 512 * 100,000 bytes = 50M .. perfect! To specify a size in K or M, you need to specify so.

>
> 2) Should the user session level MAX_DUMP_FILE_SIZE value be less than
> that of INIT.ORA value.

I don't think so.

>
> Any suggestions/comments highly appreciated.
>
> - gK
Received on Wed Apr 26 2006 - 15:25:21 CDT