Re: Setting SQL trace on another session

On 26 Apr 2006 12:28:24 -0700, 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

1 Are you sure you changed it for the correct session? 1a) Are you aware the unit for this parameter is in 512 bytes blocks? 1b) Are you aware of the value unlimited for this parameter 2 No, why should it.

--
Sybrand Bakker, Senior Oracle DBA