RE: 2G trace files - solved...sort of
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: 2G trace files - solved...sort of
Lisa, Have you're SA set the ulimit to unlimited, or you can use the filesize parameter in export to break the export into several files. hth ..JIM... [EMAIL PROTECTED] 10/30/03 8:39:26 AM 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: 2G trace files - solved...sort of
I think if you are having 32 bit Oracle software, this is the limit. There is a document on Metalink specifying export dump size limits with 32 and 64 bit software. Other listers already suggested you couple of options to resolve this issue. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 30 Oct 2003 06:54:32 -0800 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: 2G trace files - solved...sort of
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
