Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Setting SQL trace on another session

Re: Setting SQL trace on another session

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 26 Apr 2006 22:18:09 +0200
Message-ID: <r7lv42lrmdkghm584lja5mp6dods5ndt07@4ax.com>


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
Received on Wed Apr 26 2006 - 15:18:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US