RE: 2G trace files - solved...sort of

Sorry Lisa,

This doesn't work for export files. Sounds like you are hitting an OS limit. What are you running Oracle on. You can export to a compressed file via a UNIX pipe. (if indeed it is UNIX you are running on).

Regards

Lee

-----Original Message-----
Sent: 30 October 2003 14:39
To: Multiple recipients of list ORACLE-L

Hi Guys,
We are currently unable to do a full database export as it fails due to dump file being over 2GB.
Will setting this parameter to unlimited get round the problem?

TIA,
Lisa.

-----Original Message-----
Sent: 29 October 2003 21:15
To: Multiple recipients of list ORACLE-L

Thanks to everyone who responded. It turns out initialization parameter max_dump_file_size was set to *20M* and the trace files were getting cut off at that point.

I didn't look at the ls closely enough and thought it was getting cut off at 2G. Apparently the init parameter overrides the session parameter....

On Wed, 2003-10-29 at 15:09, Quintin, Richard wrote:
> >>> If I set max_dump_file_size greater than 2G I get an error.
>
> Perhaps I should be more specific:
>
> sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
> 'max_dump_file_size', 'unlimited');
> gives:
> ORA-06502: PL/SQL: numeric or value error: character to number
> conversion error
>
> sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
> 'max_dump_file_size', 2147483647 + 1);
> gives:
> ORA-01426: numeric overflow
>
>
> On Wed, 2003-10-29 at 14:24, Tim Fleury wrote:
> > Set the dump file size to unlimited.
> >
> > -----Original Message-----
> > Sent: Wednesday, October 29, 2003 10:49 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > I'm tracing a session with 10046 event level 8. Here's the method I
> > use:
> >
> > sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE );
> > sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
> > 'timed_statistics', true);
> > /* Max dump file size is 2G */
> > sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
> > 'max_dump_file_size', 2147483647);
> > sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
> >
> > If I set max_dump_file_size greater than 2G I get an error. But with
> > trace level 8, I'm easily overrunning this limit. How do you guys get
> > around this?
> >
> > BTW - Just got Optimizing Oracle Performance last night and if I didn't
> > have to work so much I would have read it through by now. Maybe I'll
> > take tomorrow off.
> >
> > Richard Quintin, DBA
> > Information Systems & Computing, DBMS
> > Virginia Tech
> > --
> > "When the character of a man is not clear to you, look at his friends."
> > -- Japanese Proverb
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Quintin, Richard
> > INET: rquintin_at_vt.edu
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
> > message BODY, include a line containing: UNSUB ORACLE-L (or the name of
> > mailing list you want to be removed from). You may also send the HELP
> > command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> Richard Quintin, DBA
> Information Systems & Computing, DBMS
> Virginia Tech
> --
> "[Long hair] is considered bohemian, which may be why I grew it, but I
> keep it long because I love the way it feels, part cloak, part fan, part
> mane, part security blanket." -- Marge Piercy
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
Richard Quintin, DBA
Information Systems & Computing, DBMS
Virginia Tech

-- 
"If you would persuade, you must appeal to interest rather than
intellect." -- Benjamin Franklin
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: rquintin_at_vt.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
----------------------------------------------------------------------------
----------------------------------------
This message is intended only for the use of the person(s) ("Intended
Recipient") to whom it is addressed. It may contain information, which is
privileged and confidential. Accordingly any dissemination, distribution,
copying or other use of this message or any of its content by any person
other than the Intended Recipient may constitute a breach of civil or
criminal law and is strictly prohibited. If you are not the Intended
Recipient, please contact the sender as soon as possible.

________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dobson, Lisa
  INET: LISA.DOBSON_at_HOMEGROUP.ORG.UK

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).







**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Robertson Lee - lerobe
  INET: lerobe_at_acxiom.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).