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
set mdfs=unlimited Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, October 29, 2003 1:49 PM 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 ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
Re: 2G trace files
Sounds like an OS limit. What OS is this on? Quintin, Richard [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/29/2003 10:49 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:2G trace files 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).
RE: 2G trace files
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 -- Author: Tim Fleury 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).
Re: 2G trace files
Richard, Are you sure that you are targeting your diagnostic efforts appropriately? If all users are using dedicated servers, then each trace file should only have the info for one session. If your interval for a statspack report is an entire week, its going to be pretty tough to find the particular query that you're looking for. Are you tracing all sessions, all the time? The feds don't put up routine checkpoints on federal interstates just to check for expired registration. It would bottleneck the entire system and generate too much info to be processed effectively. From the Heisenburg angle, I would think that generating such a large amount of trace would clearly be impacting the server's I/O subsystem in a big way. Pd "Quintin, Richard" [EMAIL PROTECTED] wrote: I'm tracing a session with 10046 event level 8. Here's the method Iuse: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 withtrace level 8, I'm easily overrunning this limit. How do you guys getaround this?BTW - Just got Optimizing Oracle Performance last night and if I didn'thave to work so much I would have read it through by now. Maybe I'lltake tomorrow off.Richard Quintin, DBA Information Systems Computing, DBMS Virginia Tech -- "Whe! n 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, RichardINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Exclusive Video Premiere - Britney Spears
RE: 2G trace files
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 -- 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).
Re: 2G trace files
Yes. I'm tracing a single session. What made you think I wasn't? What does statspack have to do with this? 2G is not such a large amount... On Wed, 2003-10-29 at 14:44, Paul Drake wrote: Richard, Are you sure that you are targeting your diagnostic efforts appropriately? If all users are using dedicated servers, then each trace file should only have the info for one session. If your interval for a statspack report is an entire week, its going to be pretty tough to find the particular query that you're looking for. Are you tracing all sessions, all the time? The feds don't put up routine checkpoints on federal interstates just to check for expired registration. It would bottleneck the entire system and generate too much info to be processed effectively. From the Heisenburg angle, I would think that generating such a large amount of trace would clearly be impacting the server's I/O subsystem in a big way. Pd Quintin, Richard [EMAIL PROTECTED] wrote: 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 -- Whe! n 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). __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears Richard Quintin, DBA Information Systems Computing, DBMS Virginia Tech -- If you haven't forgiven yourself something, how can you forgive others? -- Dolores Huerta -- 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).
RE: 2G trace files
I believe that is a limitation with that procedure. If you are tracing your own session, use Alter session set max_dump_file_size=unlimited; Otherwise set it at the system level during your trace Alter system set max_dump_file_size=unlimited; If necessary, reset it after your large trace has completed. -Original Message- Sent: Wednesday, October 29, 2003 12:09 PM To: Multiple recipients of list ORACLE-L 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 -- 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 -- Author: Tim Fleury 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).
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).
