RE: ORA-04031
0.0 -Original Message- Sent: Friday, June 27, 2003 6:10 PM To: Multiple recipients of list ORACLE-L What patch level of 8.1.7 are you on?? --Walt --- Schauss, Peter [EMAIL PROTECTED] wrote: One of our third party applications gave us the following message: ORA-04031: unable to allocate 4192 bytes of shared memory Environment is Oracle 8.1.7 on AIX 4.3.2 The output from select * from v$sgastat showed about 3 mb free out of a total of 40 mb of shared pool. I increased the size of shared pool to 60 mb and started to watch the shared pool/free memory value in v$sgastat. It seems to vary between about 3 and 11 mb. 1. What is the application likely to be doing that requires it to allocate shared pool? 2. How do I know when I have the shared pool value in init.ora set high enough so that I won't get the ORA-04031 errors? 3. Is there any other init.ora parameter I ought to be looking at here? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Weaver, Walt 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: Schauss, Peter 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: ORA-04031
what the value of your large pool ?? -Original Message- Sent: Monday, June 30, 2003 3:05 PM To: Multiple recipients of list ORACLE-L 0.0 -Original Message- Sent: Friday, June 27, 2003 6:10 PM To: Multiple recipients of list ORACLE-L What patch level of 8.1.7 are you on?? --Walt --- Schauss, Peter [EMAIL PROTECTED] wrote: One of our third party applications gave us the following message: ORA-04031: unable to allocate 4192 bytes of shared memory Environment is Oracle 8.1.7 on AIX 4.3.2 The output from select * from v$sgastat showed about 3 mb free out of a total of 40 mb of shared pool. I increased the size of shared pool to 60 mb and started to watch the shared pool/free memory value in v$sgastat. It seems to vary between about 3 and 11 mb. 1. What is the application likely to be doing that requires it to allocate shared pool? 2. How do I know when I have the shared pool value in init.ora set high enough so that I won't get the ORA-04031 errors? 3. Is there any other init.ora parameter I ought to be looking at here? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Weaver, Walt 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: Schauss, Peter 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 electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala 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
RE: ORA-04031
600k -Original Message- Sent: Monday, June 30, 2003 11:10 AM To: Multiple recipients of list ORACLE-L what the value of your large pool ?? -Original Message- Sent: Monday, June 30, 2003 3:05 PM To: Multiple recipients of list ORACLE-L 0.0 -Original Message- Sent: Friday, June 27, 2003 6:10 PM To: Multiple recipients of list ORACLE-L What patch level of 8.1.7 are you on?? --Walt --- Schauss, Peter [EMAIL PROTECTED] wrote: One of our third party applications gave us the following message: ORA-04031: unable to allocate 4192 bytes of shared memory Environment is Oracle 8.1.7 on AIX 4.3.2 The output from select * from v$sgastat showed about 3 mb free out of a total of 40 mb of shared pool. I increased the size of shared pool to 60 mb and started to watch the shared pool/free memory value in v$sgastat. It seems to vary between about 3 and 11 mb. 1. What is the application likely to be doing that requires it to allocate shared pool? 2. How do I know when I have the shared pool value in init.ora set high enough so that I won't get the ORA-04031 errors? 3. Is there any other init.ora parameter I ought to be looking at here? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Weaver, Walt 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: Schauss, Peter 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 electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
ORA-04031
One of our third party applications gave us the following message: ORA-04031: unable to allocate 4192 bytes of shared memory Environment is Oracle 8.1.7 on AIX 4.3.2 The output from select * from v$sgastat showed about 3 mb free out of a total of 40 mb of shared pool. I increased the size of shared pool to 60 mb and started to watch the shared pool/free memory value in v$sgastat. It seems to vary between about 3 and 11 mb. 1. What is the application likely to be doing that requires it to allocate shared pool? 2. How do I know when I have the shared pool value in init.ora set high enough so that I won't get the ORA-04031 errors? 3. Is there any other init.ora parameter I ought to be looking at here? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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: ORA-04031
Peter: this error usually tells you that you do not have enough memory... you should increase your shared_pool_size parameter in your init.ora ... If you need to determine a shared pool size for avoiding this error, I can send you a script that helps you to 'size' your sga... If you are interested send me a mail off-list and I'll start to look for it :-) and send it to you!. HTH JL --- Schauss, Peter [EMAIL PROTECTED] wrote: One of our third party applications gave us the following message: ORA-04031: unable to allocate 4192 bytes of shared memory Environment is Oracle 8.1.7 on AIX 4.3.2 The output from select * from v$sgastat showed about 3 mb free out of a total of 40 mb of shared pool. I increased the size of shared pool to 60 mb and started to watch the shared pool/free memory value in v$sgastat. It seems to vary between about 3 and 11 mb. 1. What is the application likely to be doing that requires it to allocate shared pool? 2. How do I know when I have the shared pool value in init.ora set high enough so that I won't get the ORA-04031 errors? 3. Is there any other init.ora parameter I ought to be looking at here? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: ORA-04031
What patch level of 8.1.7 are you on?? --Walt --- Schauss, Peter [EMAIL PROTECTED] wrote: One of our third party applications gave us the following message: ORA-04031: unable to allocate 4192 bytes of shared memory Environment is Oracle 8.1.7 on AIX 4.3.2 The output from select * from v$sgastat showed about 3 mb free out of a total of 40 mb of shared pool. I increased the size of shared pool to 60 mb and started to watch the shared pool/free memory value in v$sgastat. It seems to vary between about 3 and 11 mb. 1. What is the application likely to be doing that requires it to allocate shared pool? 2. How do I know when I have the shared pool value in init.ora set high enough so that I won't get the ORA-04031 errors? 3. Is there any other init.ora parameter I ought to be looking at here? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Weaver, Walt 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: ORA-04031
Peter You might take a look at Note 146599.1 on Metalink, Diagnosing and Resolving Error ORA-04031. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, June 27, 2003 2:30 PM To: Multiple recipients of list ORACLE-L One of our third party applications gave us the following message: ORA-04031: unable to allocate 4192 bytes of shared memory Environment is Oracle 8.1.7 on AIX 4.3.2 The output from select * from v$sgastat showed about 3 mb free out of a total of 40 mb of shared pool. I increased the size of shared pool to 60 mb and started to watch the shared pool/free memory value in v$sgastat. It seems to vary between about 3 and 11 mb. 1. What is the application likely to be doing that requires it to allocate shared pool? 2. How do I know when I have the shared pool value in init.ora set high enough so that I won't get the ORA-04031 errors? 3. Is there any other init.ora parameter I ought to be looking at here? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter 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: DENNIS WILLIAMS 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).
Export generates ORA-04031 error
I received following errors during export. I had increased shared_pool_size in configuration file several times, it just fixed the problem for couple weeks then now I received the same problem. Is there a better way to fix this issue permanently? And what is the maximum limit size can I increase shared_pool_size? Currenlty it is increased from 500 to 900. EXP-8: ORACLE error 4031 encountered ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN :1 := SYS.DBMS_REFR...,PL/SQL MPCODE,BAMIMA: Bam Buffer) EXP-00083: The previous problem occurred when calling SYS.DBMS_REFRESH_EXP_SITES.schema_info_expacle Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nguyen, David M 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: Export generates ORA-04031 error
9M is not a large shared pool. What db version? What app? We really do need some details to provide intelligent responces. You might consider bumping it to 20M and see what happens. Without more information it is impossible to be more specific. Allan -Original Message- Sent: Monday, February 24, 2003 9:59 AM To: Multiple recipients of list ORACLE-L I received following errors during export. I had increased shared_pool_size in configuration file several times, it just fixed the problem for couple weeks then now I received the same problem. Is there a better way to fix this issue permanently? And what is the maximum limit size can I increase shared_pool_size? Currenlty it is increased from 500 to 900. EXP-8: ORACLE error 4031 encountered ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN :1 := SYS.DBMS_REFR...,PL/SQL MPCODE,BAMIMA: Bam Buffer) EXP-00083: The previous problem occurred when calling SYS.DBMS_REFRESH_EXP_SITES.schema_info_expacle Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nguyen, David M 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 email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan 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: ORA-04031 error
brain_damage, It seems you are just running a small database, why using shared server? MTS in linux/816 has severe bug, I suggest you move to dedicated server? And in shared server mode,sort_area_size is allocated from sga, not the pga, but does it come from the large pool? Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-07 17:28:00 ,you wrote£º=== Hi List, whats the meaning of the following error ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (large pool,unknown object,sort subheap,sort key) heres the sql query ---select * from hox12 order by op desc; Oracle Release --8.1.6.1.0 OS -- RH 7.1 sort_area_size-- 2MB Temporary Tablespace-- 25MB Large pool-- 10MB Server Type-- Shared TIA Tc -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: chao_ping 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).
ORA-04031 error
Hi List, whats the meaning of the following error ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (large pool,unknown object,sort subheap,sort key) heres the sql query ---select * from hox12 order by op desc; Oracle Release --8.1.6.1.0 OS -- RH 7.1 sort_area_size-- 2MB Temporary Tablespace-- 25MB Large pool-- 10MB Server Type-- Shared TIA Tc -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: ORA-04031 error
Hi Tc, Just a curious that your OS can't allocate share memory that you set at large pool, try to decrease your large pool. I ever get this error and that way is working. Rgrds, Sony -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Saturday, February 08, 2003 8:29 AM To: Multiple recipients of list ORACLE-L Subject: ORA-04031 error Hi List, whats the meaning of the following error ERROR at line 1: ORA-04031: unable to allocate 4096 bytes of shared memory (large pool,unknown object,sort subheap,sort key) heres the sql query ---select * from hox12 order by op desc; Oracle Release --8.1.6.1.0 OS -- RH 7.1 sort_area_size-- 2MB Temporary Tablespace-- 25MB Large pool-- 10MB Server Type-- Shared TIA Tc -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Sony kristanto 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: ORA-04031
I happened when your shared pool is too small. You probably need to increase it. You can also clear the shared pool before the export but this will cause some degradation afterwards because whoever uses a procedure or function for the first time will have to wait for it to be loaded from the database. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 20, 2002 8:48 AM I received ORA-04031 while exporting database. Can someone explain what causes it and how to fix it? . exporting post-schema procedural objects and actions EXP-8: ORACLE error 4031 encountered ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN :1 := SYS.DBMS_REFR...,PL/SQL MPCODE,BAMIMA: Bam Buffer) EXP-00083: The previous problem occurred when calling SYS.DBMS_REFRESH_EXP_SITES.schema_info_exp . exporting statistics Export terminated successfully with warnings. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nguyen, David M 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: Yechiel Adar 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: ORA-04031
Title: RE: ORA-04031 Hi David, I ran into this too. Try the following: 1. flush shared pool, re-execute 2. bounce database. IN addition if you search on this on Metalink you will find lots of info. HTH Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Nguyen, David M [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 1:49 AM To: Multiple recipients of list ORACLE-L Subject: ORA-04031 I received ORA-04031 while exporting database. Can someone explain what causes it and how to fix it? . exporting post-schema procedural objects and actions EXP-8: ORACLE error 4031 encountered ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN :1 := SYS.DBMS_REFR...,PL/SQL MPCODE,BAMIMA: Bam Buffer) EXP-00083: The previous problem occurred when calling SYS.DBMS_REFRESH_EXP_SITES.schema_info_exp . exporting statistics Export terminated successfully with warnings. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nguyen, David M 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: ORA-04031
David, What OS and Oracle version? At least in 8.1.7.0.0 on HPUX that error message could be the result of a bug - we get it every few weeks on our production financials database and have to bounce the DB. Flushing the Shared Pool every night doesn't prevent it. Eventually we'll patch to 8.1.7.4 where it's supposedly fixed. This from Metalink (didn't copy the ID of the doc I found this in): If you are receiving ORA-4031 errors which reference BAMIMA: Bam Buffer, please apply the 8.1.7.4 patchset. It is possible that you are hitting bug 2104071 which is fixed in that patchset. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Nguyen, David M david.m.nguyen@xTo: Multiple recipients of list ORACLE-L o.com[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: ORA-04031 12/20/2002 12:48 AM Please respond to ORACLE-L I received ORA-04031 while exporting database. Can someone explain what causes it and how to fix it? . exporting post-schema procedural objects and actions EXP-8: ORACLE error 4031 encountered ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN :1 := SYS.DBMS_REFR...,PL/SQL MPCODE,BAMIMA: Bam Buffer) EXP-00083: The previous problem occurred when calling SYS.DBMS_REFRESH_EXP_SITES.schema_info_exp . exporting statistics Export terminated successfully with warnings. Thanks, David -- Author: Nguyen, David M INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
ORA-04031
I received ORA-04031 while exporting database. Can someone explain what causes it and how to fix it? . exporting post-schema procedural objects and actions EXP-8: ORACLE error 4031 encountered ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,BEGIN :1 := SYS.DBMS_REFR...,PL/SQL MPCODE,BAMIMA: Bam Buffer) EXP-00083: The previous problem occurred when calling SYS.DBMS_REFRESH_EXP_SITES.schema_info_exp . exporting statistics Export terminated successfully with warnings. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nguyen, David M 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: ORA-04031
I agree with Mark on this. I had this problem on a 8.1.7.0 and it was solved after the upgrade. (8.1.7.3) If you're already above 8.1.7.3, the problem could be fragmentation. Are you having any contention in the shared pool latch? greetings DC - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 2:08 AM Um, version? (To at least 4 places, please!) On the off change you are on 8.1.7.x, where x 3, please upgrade to 8.1.7.4. There were several bugs related to ORA-4031 from 8.1.7.0 through at least 8.1.7.2. -Mark On Wed, 2002-09-11 at 18:14, Seema Singh wrote: Hi One of instance are showing ORA-04031 error more frequntly(2 times in a week).I increased SGA ,pinned some of DBMS packages,flush SGA etc but all these are not helping at all.Let me know what could be look next. Thx -Seema _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Diego Cutrone INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
its 8.1.7.4 From: Mark J. Bobak [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: ORA-04031 Date: Wed, 11 Sep 2002 21:08:18 -0800 Um, version? (To at least 4 places, please!) On the off change you are on 8.1.7.x, where x 3, please upgrade to 8.1.7.4. There were several bugs related to ORA-4031 from 8.1.7.0 through at least 8.1.7.2. -Mark On Wed, 2002-09-11 at 18:14, Seema Singh wrote: Hi One of instance are showing ORA-04031 error more frequntly(2 times in a week).I increased SGA ,pinned some of DBMS packages,flush SGA etc but all these are not helping at all.Let me know what could be look next. Thx -Seema _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
As one who has been there, I can sympathize. I had a TAR open for months going back and forth with Oracle Support. The entire problem boiled down to the fact that Oracle Corporation does not have a supported method (supported is the key word) to query the free chunks of the shared pool to determine the amount of fragmentation. Fragmentation of the shared pool was the most likely cause of our ORA-4031 errors, but because Oracle Support said it couldn't be measured, I wasn't able to measure the effect of my changes to the shared pool size and the pinning. Their answer was bump up the shared pool, bounce the instance, repeat until it stopped. So, completely frustrated, I ended up on Steve Adams' site at http://www.ixora.com.au There are some great QA sections there about dealing with ORA-4031s. I also purchased Steve's book, Oracle8i Internal Services (http://www.oreilly.com/catalog/orinternals/) from http://www.bookpool.com The book complements the website by going in-depth into some of the X$ views to get info on the shared pool and other structures. I am by no means an expert on the shared pool, but we were able to solve our ORA-4031s with help from these resources. GL! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 2:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: ORA-04031 its 8.1.7.4 From: Mark J. Bobak [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: ORA-04031 Date: Wed, 11 Sep 2002 21:08:18 -0800 Um, version? (To at least 4 places, please!) On the off change you are on 8.1.7.x, where x 3, please upgrade to 8.1.7.4. There were several bugs related to ORA-4031 from 8.1.7.0 through at least 8.1.7.2. -Mark On Wed, 2002-09-11 at 18:14, Seema Singh wrote: Hi One of instance are showing ORA-04031 error more frequntly(2 times in a week).I increased SGA ,pinned some of DBMS packages,flush SGA etc but all these are not helping at all.Let me know what could be look next. Thx -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
X$KSMLRU is the free list chunks. On Thu, 2002-09-12 at 17:03, Jesse, Rich wrote: As one who has been there, I can sympathize. I had a TAR open for months going back and forth with Oracle Support. The entire problem boiled down to the fact that Oracle Corporation does not have a supported method (supported is the key word) to query the free chunks of the shared pool to determine the amount of fragmentation. Fragmentation of the shared pool was the most likely cause of our ORA-4031 errors, but because Oracle Support said it couldn't be measured, I wasn't able to measure the effect of my changes to the shared pool size and the pinning. Their answer was bump up the shared pool, bounce the instance, repeat until it stopped. So, completely frustrated, I ended up on Steve Adams' site at http://www.ixora.com.au There are some great QA sections there about dealing with ORA-4031s. I also purchased Steve's book, Oracle8i Internal Services (http://www.oreilly.com/catalog/orinternals/) from http://www.bookpool.com The book complements the website by going in-depth into some of the X$ views to get info on the shared pool and other structures. I am by no means an expert on the shared pool, but we were able to solve our ORA-4031s with help from these resources. GL! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 2:18 PM To: Multiple recipients of list ORACLE-L Subject: Re: ORA-04031 its 8.1.7.4 From: Mark J. Bobak [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: ORA-04031 Date: Wed, 11 Sep 2002 21:08:18 -0800 Um, version? (To at least 4 places, please!) On the off change you are on 8.1.7.x, where x 3, please upgrade to 8.1.7.4. There were several bugs related to ORA-4031 from 8.1.7.0 through at least 8.1.7.2. -Mark On Wed, 2002-09-11 at 18:14, Seema Singh wrote: Hi One of instance are showing ORA-04031 error more frequntly(2 times in a week).I increased SGA ,pinned some of DBMS packages,flush SGA etc but all these are not helping at all.Let me know what could be look next. Thx -Seema -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
Also check out Metalink note 146599.1 which is on diagnosing ora-4031 errors. This has helped me. Also you can consider adding an event in your init.ora file like 4031 trace name errorstack level 3 This will dump an errorstack when the 4031 error occurs - this can be interpreted by Oracle support. However, realise those files will take up room and that when the error occurs the user will experience a delay whilst the dump file is written. HTH, Bruce Reardon -Original Message- X$KSMLRU is the free list chunks. On Thu, 2002-09-12 at 17:03, Jesse, Rich wrote: As one who has been there, I can sympathize. I had a TAR open for months going back and forth with Oracle Support. The entire problem boiled down to the fact that Oracle Corporation does not have a supported method (supported is the key word) to query the free chunks of the shared pool to determine the amount of fragmentation. Fragmentation of the shared pool was the most likely cause of our ORA-4031 errors, but because Oracle Support said it couldn't be measured, I wasn't able to measure the effect of my changes to the shared pool size and the pinning. Their answer was bump up the shared pool, bounce the instance, repeat until it stopped. So, completely frustrated, I ended up on Steve Adams' site at http://www.ixora.com.au There are some great QA sections there about dealing with ORA-4031s. I also purchased Steve's book, Oracle8i Internal Services (http://www.oreilly.com/catalog/orinternals/) from http://www.bookpool.com The book complements the website by going in-depth into some of the X$ views to get info on the shared pool and other structures. I am by no means an expert on the shared pool, but we were able to solve our ORA-4031s with help from these resources. GL! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Seema Singh [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 2:18 PM its 8.1.7.4 From: Mark J. Bobak [EMAIL PROTECTED] Date: Wed, 11 Sep 2002 21:08:18 -0800 Um, version? (To at least 4 places, please!) On the off change you are on 8.1.7.x, where x 3, please upgrade to 8.1.7.4. There were several bugs related to ORA-4031 from 8.1.7.0 through at least 8.1.7.2. -Mark On Wed, 2002-09-11 at 18:14, Seema Singh wrote: Hi One of instance are showing ORA-04031 error more frequntly(2 times in a week).I increased SGA ,pinned some of DBMS packages,flush SGA etc but all these are not helping at all.Let me know what could be look next. Thx -Seema -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ORA-04031
Hi One of instance are showing ORA-04031 error more frequntly(2 times in a week).I increased SGA ,pinned some of DBMS packages,flush SGA etc but all these are not helping at all.Let me know what could be look next. Thx -Seema _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
Seema: I faced this problem a couple of months back. The combination of increasing the SGA and reducing the hash_area_size from 20 megs to 4 megs helped me out. Also, I am running MTS and I had not configured the large pool. Configuring large pool made a huge difference. I'm appending clips from Raj and Tim. There's some explanation of why reducing hash_area_size might help. Do you have the full text from the error message? This might help us also. Good luck! From Raj - - - - - I don't know if it matters, but we faced the same error in 9x, and when we set the hash_area_size to 1M, it went away. The exact error message for us was ORA-04031: unable to allocate 1126656 bytes of shared memory (shared pool,unknown object,hash-join subh,kllcqc:kllcqslt) The 3rd parameter is hash-join subh, so I think it pertains to hash joins. For Barb, the third parameter is cursor work he(ap) so I wonder would it pertain to hash_area_size at all? Raj From Tim - - - - - I did an advanced search on MetaLink for kllcqc, making sure to check the checkbox for Bug Database -- quite a few bugs appeared (for what they are worth). One of them (#2324210) is against 9.0.1.3 on Solaris, but the error message looks remarkably like yours even so. Like you, they are using MTS. They indicate that the settings for SORT_AREA_SIZE and HASH_AREA_SIZE are too large for the Shared Pool, hence the ORA-04031. The solution is to reduce SORT_AREA_SIZE and HASH_AREA_SIZE... What are the settings for SORT_AREA_SIZE and HASH_AREA_SIZE here? Is it possible that the users may be using ALTER SESSION SET to set their own custom values for these parameters? I think this statement should appear in the V$SQL or V$SQLAREA if they are using it. This would possibly explain the sudden (and violent) onset of these symptoms... -- From: Seema Singh[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Wednesday, September 11, 2002 4:14 PM To: Multiple recipients of list ORACLE-L Subject: ORA-04031 Hi One of instance are showing ORA-04031 error more frequntly(2 times in a week).I increased SGA ,pinned some of DBMS packages,flush SGA etc but all these are not helping at all.Let me know what could be look next. Thx -Seema _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
Um, version? (To at least 4 places, please!) On the off change you are on 8.1.7.x, where x 3, please upgrade to 8.1.7.4. There were several bugs related to ORA-4031 from 8.1.7.0 through at least 8.1.7.2. -Mark On Wed, 2002-09-11 at 18:14, Seema Singh wrote: Hi One of instance are showing ORA-04031 error more frequntly(2 times in a week).I increased SGA ,pinned some of DBMS packages,flush SGA etc but all these are not helping at all.Let me know what could be look next. Thx -Seema _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ora-04031 and x$ksmlru
Oracle 8.0.5 Solaris 2.6 shared_pool_reserved_min_alloc5K shared_pool_reserved_size 6656000 shared_pool_size 13312 total sga size is 597 megs I'm fighting a particularly difficult ora-04031 error. The error can be reproduced easily with several queries, including this one: select a.agreement fromadvdb.ad a, advdb.pub p where p.adno=a.adno and p.vno=a.vno; ERROR: ORA-04031: unable to allocate 340032 bytes of shared memory (shared pool,unknown object,cursor work he,kllcqc:kllcqslt) In reviewing metalink article 146599.1, it says that I can determine allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation This query (select * from x$ksmlru where ksmlrsiz0) returned this: ADDR INDXINST_ID KSMLRCOM KSMLRSIZ KSMLRNUM -- -- -- -- KSMLRHON KSMLROHV KSMLRSES -- 82B8 0 1 sort area 4152 8 0 A42C4048 82FC 1 1 kafco : qkacol 4292 1032 insert into pub (adno,pubno,... 1730627729 A427B7E0 8340 2 1 kllcqc:kllcqslt 324100 13311 0 A438EC84 8384 3 1 kllcqc:kllcqslt 326124 9590 0 A433A828 83C8 4 1 kllcqc:kllcqslt 376908 6326 0 A438EC84 840C 5 1 kllcqc:kllcqslt 384268 10731 0 A433A828 I notice several references in the column KSMLRCOM to kllcq:kllcqslt, which matches information from the error message. However, I have no idea what that means. Can the information from this column in some way help me figure out what the problem is? Since my request_failures is 41 and my last_failure_size 384,268 and shared_pool_reserved_min_alloc is 5k, I believe I need to increase shared_pool_size. I'm currently begging for permission to bounce the database, but the timing isn't great . . . I've flushed shared_pool several times,to no avail. Anything I can do till I can bounce the database?? Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ora-04031 and x$ksmlru
Thanks to the wonderful search capabilities that Steve Adams has installed on his website at www.ixora.com.au, the following page has some more information about the X$KSMLRU fixed-table (http://www.zoftware.org/tuning/tune_shared_pool.html#fixed_table)... I did an advanced search on MetaLink for kllcqc, making sure to check the checkbox for Bug Database -- quite a few bugs appeared (for what they are worth). One of them (#2324210) is against 9.0.1.3 on Solaris, but the error message looks remarkably like yours even so. Like you, they are using MTS. They indicate that the settings for SORT_AREA_SIZE and HASH_AREA_SIZE are too large for the Shared Pool, hence the ORA-04031. The solution is to reduce SORT_AREA_SIZE and HASH_AREA_SIZE... What are the settings for SORT_AREA_SIZE and HASH_AREA_SIZE here? Is it possible that the users may be using ALTER SESSION SET to set their own custom values for these parameters? I think this statement should appear in the V$SQL or V$SQLAREA if they are using it. This would possibly explain the sudden (and violent) onset of these symptoms... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 9:33 AM Oracle 8.0.5 Solaris 2.6 shared_pool_reserved_min_alloc5K shared_pool_reserved_size 6656000 shared_pool_size 13312 total sga size is 597 megs I'm fighting a particularly difficult ora-04031 error. The error can be reproduced easily with several queries, including this one: select a.agreement fromadvdb.ad a, advdb.pub p where p.adno=a.adno and p.vno=a.vno; ERROR: ORA-04031: unable to allocate 340032 bytes of shared memory (shared pool,unknown object,cursor work he,kllcqc:kllcqslt) In reviewing metalink article 146599.1, it says that I can determine allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation This query (select * from x$ksmlru where ksmlrsiz0) returned this: ADDR INDXINST_ID KSMLRCOM KSMLRSIZ KSMLRNUM -- -- -- -- KSMLRHON KSMLROHV KSMLRSES -- 82B8 0 1 sort area 4152 8 0 A42C4048 82FC 1 1 kafco : qkacol 4292 1032 insert into pub (adno,pubno,... 1730627729 A427B7E0 8340 2 1 kllcqc:kllcqslt 324100 13311 0 A438EC84 8384 3 1 kllcqc:kllcqslt 326124 9590 0 A433A828 83C8 4 1 kllcqc:kllcqslt 376908 6326 0 A438EC84 840C 5 1 kllcqc:kllcqslt 384268 10731 0 A433A828 I notice several references in the column KSMLRCOM to kllcq:kllcqslt, which matches information from the error message. However, I have no idea what that means. Can the information from this column in some way help me figure out what the problem is? Since my request_failures is 41 and my last_failure_size 384,268 and shared_pool_reserved_min_alloc is 5k, I believe I need to increase shared_pool_size. I'm currently begging for permission to bounce the database, but the timing isn't great . . . I've flushed shared_pool several times,to no avail. Anything I can do till I can bounce the database?? Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: ora-04031 and x$ksmlru
Tim, I don't know if it matters, but we faced the same error in 9x, and when we set the hash_area_size to 1M, it went away. The exact error message for us was ORA-04031: unable to allocate 1126656 bytes of shared memory (shared pool,unknown object,hash-join subh,kllcqc:kllcqslt) The 3rd parameter is hash-join subh, so I think it pertains to hash joins. For Barb, the third parameter is cursor work he(ap) so I wonder would it pertain to hash_area_size at all? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 16, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Thanks to the wonderful search capabilities that Steve Adams has installed on his website at www.ixora.com.au, the following page has some more information about the X$KSMLRU fixed-table (http://www.zoftware.org/tuning/tune_shared_pool.html#fixed_table)... I did an advanced search on MetaLink for kllcqc, making sure to check the checkbox for Bug Database -- quite a few bugs appeared (for what they are worth). One of them (#2324210) is against 9.0.1.3 on Solaris, but the error message looks remarkably like yours even so. Like you, they are using MTS. They indicate that the settings for SORT_AREA_SIZE and HASH_AREA_SIZE are too large for the Shared Pool, hence the ORA-04031. The solution is to reduce SORT_AREA_SIZE and HASH_AREA_SIZE... What are the settings for SORT_AREA_SIZE and HASH_AREA_SIZE here? Is it possible that the users may be using ALTER SESSION SET to set their own custom values for these parameters? I think this statement should appear in the V$SQL or V$SQLAREA if they are using it. This would possibly explain the sudden (and violent) onset of these symptoms... *2 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. *2
RE: ora-04031 and x$ksmlru
NAMETYPEVALUE
--- --- --
sort_area_retained_size integer 0
sort_area_size integer 2097152
hash_area_size integer 20971520
The developers might (very possibly) be using alter session set, but not
the regular users -- they're locked into that silly application stuff.
Still trying to devour all the statistics I've accumulated.
Thanks so much!
Barb
--
From: Tim Gorman[SMTP:[EMAIL PROTECTED]]
Reply To: [EMAIL PROTECTED]
Sent: Thursday, May 16, 2002 12:13 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: ora-04031 and x$ksmlru
Thanks to the wonderful search capabilities that Steve Adams has installed
on his website at www.ixora.com.au, the following page has some more
information about the X$KSMLRU fixed-table
(http://www.zoftware.org/tuning/tune_shared_pool.html#fixed_table)...
I did an advanced search on MetaLink for kllcqc, making sure to check
the checkbox for Bug Database -- quite a few bugs appeared (for what
they
are worth). One of them (#2324210) is against 9.0.1.3 on Solaris, but the
error message looks remarkably like yours even so. Like you, they are
using
MTS. They indicate that the settings for SORT_AREA_SIZE and
HASH_AREA_SIZE
are too large for the Shared Pool, hence the ORA-04031. The solution is
to
reduce SORT_AREA_SIZE and HASH_AREA_SIZE...
What are the settings for SORT_AREA_SIZE and HASH_AREA_SIZE here? Is it
possible that the users may be using ALTER SESSION SET to set their own
custom values for these parameters? I think this statement should
appear
in the V$SQL or V$SQLAREA if they are using it. This would possibly
explain
the sudden (and violent) onset of these symptoms...
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, May 16, 2002 9:33 AM
Oracle 8.0.5
Solaris 2.6
shared_pool_reserved_min_alloc5K
shared_pool_reserved_size 6656000
shared_pool_size 13312
total sga size is 597 megs
I'm fighting a particularly difficult ora-04031 error. The error can
be
reproduced easily with several queries, including this one:
select a.agreement fromadvdb.ad a, advdb.pub p
where p.adno=a.adno and p.vno=a.vno;
ERROR:
ORA-04031: unable to allocate 340032 bytes of shared memory (shared
pool,unknown object,cursor work he,kllcqc:kllcqslt)
In reviewing metalink article 146599.1, it says that I can determine
allocations in the shared pool that cause other objects in the shared
pool to be aged out. This fixed table can be used to identify what is
causing the large allocation
This query (select * from x$ksmlru where ksmlrsiz0) returned this:
ADDR INDXINST_ID KSMLRCOM KSMLRSIZ
KSMLRNUM
-- -- --
--
KSMLRHON KSMLROHV KSMLRSES
--
82B8 0 1 sort area 4152
8
0 A42C4048
82FC 1 1 kafco : qkacol 4292
1032
insert into pub (adno,pubno,... 1730627729 A427B7E0
8340 2 1 kllcqc:kllcqslt 324100
13311
0 A438EC84
8384 3 1 kllcqc:kllcqslt 326124
9590
0 A433A828
83C8 4 1 kllcqc:kllcqslt 376908
6326
0 A438EC84
840C 5 1 kllcqc:kllcqslt 384268
10731
0 A433A828
I notice several references in the column KSMLRCOM to kllcq:kllcqslt,
which matches information from the error message. However, I have no
idea
what that means.
Can the information from this column in some way help me figure out
what
the problem is?
Since my request_failures is 41 and my last_failure_size 384,268 and
shared_pool_reserved_min_alloc is 5k, I believe I need to increase
shared_pool_size. I'm currently begging for permission to bounce the
database, but the timing isn't great . . .
I've flushed shared_pool several times,to no avail.
Anything I can do till I can bounce the database??
Thanks for any help!
Barb
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
Oracle error ORA-04031
I'm getting an ORA-04031: unable to allocate 8192 bytes of shared memory (large
pool,unknown object,sort subheap,sort key) error, and am having a hard time
solving the issue.
The SQL being ran is:
SELECT COUNT(DISTINCT mail) theCount
FROM Demo D
WHERE (EXISTS (SELECT 1 FROM mails WHERE mail = d.mail))
AND (D.countryID IN ('US'))
AND ((EXISTS (SELECT 1 FROM Interests I WHERE D.id = I.demoID AND I.interestID=31))
OR (EXISTS (SELECT 1 FROM Interests I WHERE D.id = I.demoID AND I.interestID=84)) )
AND unsub_date IS NULL
AND return_date IS NULL
demo is a 33+M record table, Mails is 10+M and Interests is 40+M.
There are indexes on demo.mail, Mails.mail, demo.id, and interests(interestid,demoid).
I tried doubling the large pool to see if that would help, but the same query runs
fine on 8i. This is currently on 9i.
Oh, there is also a degree of parallelism on each table, each set to 4.
Any advice would be appreciated.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Oracle error ORA-04031
[[EMAIL PROTECTED]] I'm getting an ORA-04031: unable to allocate 8192 bytes of shared memory (large pool,unknown object,sort subheap,sort key) error, and am having a hard time solving the issue. http://www.cryer.co.uk/brian/oracle/ORA04031.htm -- James Manning [EMAIL PROTECTED] GPG Key fingerprint = B913 2FBD 14A9 CE18 B2B7 9C8E A0BF B026 EEBB F6E4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Manning INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ERR: ORA-04031 unable to allocate
Take a look at Metalink note :1076835.6. You may be running out of underlying OS user process memory. Changing some kernel parameters may help. One other thing to check: Is this query going parallel (i.e. is the degree of any of the underlying objects 1 and you are seeing parallel query process slaves showing up on the server?). Regards, Chris Gait On 14 Feb 2002, at 16:03, Baker, Barbara wrote: Date sent: Thu, 14 Feb 2002 16:03:32 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ERR: ORA-04031 unable to allocate
Baker, Barbara wrote: Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' Barbara, I am always suspicious of 'very simple queries' which run for a minute, whether they fail with a mysterious error or they don't. Moreover, I always jump on my bazooka any time I see a SELECT DISTINCT, especially with a join. To me, the execution plan could probably have shed more light than V$LOCK. I guess that the poor beast is just exhausting itself trying the impossible. If I were you, I would try something such as : select v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.voluntary_reps v where a.receiver = v.name and exists (select null from advdb.pub p where p.adno = a.adno and p.vno = a.vno and p.state = 'VAR' and p.vnoflag = 'Y') or (probably better) select v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.voluntary_reps v where (a.adno, a.vno) in (select p.adno, p.vno from advdb.pub p where p.state = 'VAR' and p.vnoflag = 'Y') and a.receiver = v.name or possibly something else involving inline views. The best solution depends of course on the volume of data returned and which columns are indexed. You may well solve your problem with a query which will run much faster. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ERR: ORA-04031 unable to allocate
Stephane: Today, original query either runs in 1 minute, or errors with 4031 in 1 minute. I find that odd . . . Regardless, your examples are quite lovely! The first returns the result set in less than 1 second. Thanks for taking the time to look at this, and for the code. I really appreciate it! Barb (If you're interested . . . I've attached the explain plans for the original, and for your 2 suggestions.) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3321 Card=68805 Bytes=11628045) 10 SORT (UNIQUE) (Cost=3321 Card=68805 Bytes=11628045) 21 HASH JOIN (Cost=1605 Card=68805 Bytes=11628045) 32 TABLE ACCESS (FULL) OF 'VOLUNTARY_REPS' (Cost=1 Card=82 Bytes=2460) 42 HASH JOIN (Cost=1602 Card=83909 Bytes=11663351) 54 TABLE ACCESS (BY INDEX ROWID) OF 'PUB' (Cost=641 Card=83909 Bytes=2601179) 65 INDEX (RANGE SCAN) OF 'I_PUB_STATE' (NON-UNIQUE) (Cost=354 Card=83909) 74 TABLE ACCESS (FULL) OF 'AD' (Cost=433 Card=58415 Bytes=6308820) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=147 Card=2395 Bytes=330510) 10 FILTER 21 NESTED LOOPS (Cost=147 Card=2395 Bytes=330510) 32 TABLE ACCESS (FULL) OF 'AD' (Cost=433 Card=2921 Bytes=315468) 42 TABLE ACCESS (BY INDEX ROWID) OF 'VOLUNTARY_REPS' (Cost=1 Card=82 Bytes=2460) 54 INDEX (RANGE SCAN) OF 'VOL_REPS_IDX1' (NON-UNIQUE) 61 TABLE ACCESS (BY INDEX ROWID) OF 'PUB' (Cost=1 Card=1 Bytes=31) 76 INDEX (RANGE SCAN) OF 'I_PUB1' (UNIQUE) (Cost=3 Card=1 ) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2025 Card=68805 Bytes=11284020) 10 HASH JOIN (Cost=2025 Card=68805 Bytes=11284020) 21 TABLE ACCESS (FULL) OF 'VOLUNTARY_REPS' (Cost=1 Card=82 Bytes=2460) 31 HASH JOIN (Cost=2022 Card=83909 Bytes=11243806) 43 VIEW (Cost=1122 Card=83909 Bytes=2181634) 54 SORT (UNIQUE) (Cost=1122 Card=83909 Bytes=2601179) 65 TABLE ACCESS (BY INDEX ROWID) OF 'PUB' (Cost=641 Card=83909 Bytes=2601179) 76 INDEX (RANGE SCAN) OF 'I_PUB_STATE' (NON-UNIQUE)(Cost=354 Card=83909) 83 TABLE ACCESS (FULL) OF 'AD' (Cost=433 Card=58415 Bytes=6308820) -- From: Stephane Faroult[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, February 15, 2002 1:53 AM To: Multiple recipients of list ORACLE-L Subject: Re: ERR: ORA-04031 unable to allocate Baker, Barbara wrote: Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' Barbara, I am always suspicious of 'very simple queries' which run for a minute, whether they fail with a mysterious error or they don't. Moreover, I always jump on my bazooka any time I see a SELECT DISTINCT, especially with a join. To me, the execution plan could probably have shed more light than V$LOCK. I guess that the poor beast is just exhausting itself trying the impossible. If I were you, I would try something such as : select v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.voluntary_reps v where a.receiver = v.name and exists (select null from advdb.pub p where p.adno = a.adno and p.vno = a.vno and p.state = 'VAR' and p.vnoflag = 'Y') or (probably better) select v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.voluntary_reps v where (a.adno, a.vno) in (select p.adno, p.vno from advdb.pub p where p.state = 'VAR' and p.vnoflag = 'Y') and a.receiver = v.name - or possibly something else involving inline views. The best solution depends of course on the volume of data returned and which columns are indexed. You may well solve your problem with a query which will run much faster. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult
RE: ERR: ORA-04031 unable to allocate
Jared: I did look at this article. I'd buy that I have a shared pool problem if I were seeing other problems in the database. However, I'm still confused that I see the problem only with this 1 query, that it's so easily reproducible only for this query, and the the other 100-or-so users in the database have not experienced problems. The database was bounced last night. I did increase open_cursors, just for grins. I'll take a look this morning and see how things look. Thanks! Barb -- From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Thursday, February 14, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Subject: Re: ERR: ORA-04031 unable to allocate see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_dat abase_id=NOTp_id=146599.1 Jared Baker, Barbara [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/14/02 04:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:ERR: ORA-04031 unable to allocate Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ERR: ORA-04031 unable to allocate
Barbara - Been there done that. I have stubbornly made the same statements. This is why some co-workers say DBAs are cantankerous. My best guess is that there is something about that query. Give up, increase SHARED_POOL_SIZE. Then your coworkers will start saying how easy you are to get along with. Dennis Williams DBA (now the friendly DBA) Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, February 15, 2002 9:14 AM To: Multiple recipients of list ORACLE-L Jared: I did look at this article. I'd buy that I have a shared pool problem if I were seeing other problems in the database. However, I'm still confused that I see the problem only with this 1 query, that it's so easily reproducible only for this query, and the the other 100-or-so users in the database have not experienced problems. The database was bounced last night. I did increase open_cursors, just for grins. I'll take a look this morning and see how things look. Thanks! Barb -- From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Thursday, February 14, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Subject: Re: ERR: ORA-04031 unable to allocate see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_dat abase_id=NOTp_id=146599.1 Jared Baker, Barbara [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/14/02 04:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:ERR: ORA-04031 unable to allocate Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
RE: ERR: ORA-04031 unable to allocate
Dennis: I DO have my reputation to consider. Cantankerous is probably the nicest thing anyone's ever said about me. I actually did find the problem. One of the tables was recently re-loaded (via some Access piece-of-junk BEHIND the dba's back!), and the normaly process that analyzes tables missed this one. So the table had no statistics. I've learned some valuable lessons. I'm going to place Occum's Razor above my screen in BIG LETTERS. Thanks everyone for your replies. Barb (still cantankerous as ever) -- From: DENNIS WILLIAMS[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, February 15, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Subject: RE: ERR: ORA-04031 unable to allocate Barbara - Been there done that. I have stubbornly made the same statements. This is why some co-workers say DBAs are cantankerous. My best guess is that there is something about that query. Give up, increase SHARED_POOL_SIZE. Then your coworkers will start saying how easy you are to get along with. Dennis Williams DBA (now the friendly DBA) Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, February 15, 2002 9:14 AM To: Multiple recipients of list ORACLE-L Jared: I did look at this article. I'd buy that I have a shared pool problem if I were seeing other problems in the database. However, I'm still confused that I see the problem only with this 1 query, that it's so easily reproducible only for this query, and the the other 100-or-so users in the database have not experienced problems. The database was bounced last night. I did increase open_cursors, just for grins. I'll take a look this morning and see how things look. Thanks! Barb -- From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Thursday, February 14, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Subject:Re: ERR: ORA-04031 unable to allocate see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_dat abase_id=NOTp_id=146599.1 Jared Baker, Barbara [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/14/02 04:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:ERR: ORA-04031 unable to allocate Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: ERR: ORA-04031 unable to allocate
Barbara, What is the size of shared_pool_size and shared_pool_reserved_size.. If shared_pool_reserved_size not set then set it 10% of your shared_pool. In 8i default is 5% of shared_pool if not set. In 7.3.4 default was most possibly 0(zero)... I hope this may help in resolving your shared pool problem... As regard querry, it definately requires attention whether it is using indexes properly and indexes themselves are not much fragmented and might require rebuilding..I shall go to check indexes of all relevant tables and it is rebuilding before changing any init.ora parameters... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 15 Feb 2002 08:33:29 -0800 Barbara - Been there done that. I have stubbornly made the same statements. This is why some co-workers say DBAs are cantankerous. My best guess is that there is something about that query. Give up, increase SHARED_POOL_SIZE. Then your coworkers will start saying how easy you are to get along with. Dennis Williams DBA (now the friendly DBA) Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, February 15, 2002 9:14 AM To: Multiple recipients of list ORACLE-L Jared: I did look at this article. I'd buy that I have a shared pool problem if I were seeing other problems in the database. However, I'm still confused that I see the problem only with this 1 query, that it's so easily reproducible only for this query, and the the other 100-or-so users in the database have not experienced problems. The database was bounced last night. I did increase open_cursors, just for grins. I'll take a look this morning and see how things look. Thanks! Barb -- From:[EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent:Thursday, February 14, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Subject: Re: ERR: ORA-04031 unable to allocate see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_dat abase_id=NOTp_id=146599.1 Jared Baker, Barbara [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/14/02 04:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:ERR: ORA-04031 unable to allocate Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: ERR: ORA-04031 unable to allocate
I'm going to place Occum's Razor above my screen in BIG LETTERS. Just be careful you don't cut your throat with it. ;) Jared Baker, Barbara [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/15/02 09:38 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ERR: ORA-04031 unable to allocate Dennis: I DO have my reputation to consider. Cantankerous is probably the nicest thing anyone's ever said about me. I actually did find the problem. One of the tables was recently re-loaded (via some Access piece-of-junk BEHIND the dba's back!), and the normaly process that analyzes tables missed this one. So the table had no statistics. I've learned some valuable lessons. I'm going to place Occum's Razor above my screen in BIG LETTERS. Thanks everyone for your replies. Barb (still cantankerous as ever) -- From: DENNIS WILLIAMS[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, February 15, 2002 9:33 AM To:Multiple recipients of list ORACLE-L Subject: RE: ERR: ORA-04031 unable to allocate Barbara - Been there done that. I have stubbornly made the same statements. This is why some co-workers say DBAs are cantankerous. My best guess is that there is something about that query. Give up, increase SHARED_POOL_SIZE. Then your coworkers will start saying how easy you are to get along with. Dennis Williams DBA (now the friendly DBA) Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, February 15, 2002 9:14 AM To: Multiple recipients of list ORACLE-L Jared: I did look at this article. I'd buy that I have a shared pool problem if I were seeing other problems in the database. However, I'm still confused that I see the problem only with this 1 query, that it's so easily reproducible only for this query, and the the other 100-or-so users in the database have not experienced problems. The database was bounced last night. I did increase open_cursors, just for grins. I'll take a look this morning and see how things look. Thanks! Barb -- From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent:Thursday, February 14, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Subject: Re: ERR: ORA-04031 unable to allocate see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_dat abase_id=NOTp_id=146599.1 Jared Baker, Barbara [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/14/02 04:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:ERR: ORA-04031 unable to allocate Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services
ERR: ORA-04031 unable to allocate
Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031 unable to allocate
Barbara: You need to: 1) Have the user log out, log back on, and try again. If that fails ... 2) Bounce the instance. Before you restart, you should ... 3) Increase the size of your shared pool, and maybe ... 4) Increase the open_cursors parameter value. The Open_Cursors parameter is a *session* parameter, not an *instance* parameter, so this user may really be running out of cursors. The message seems to indicate that it's trying to allocate space for a hash join, and it cannot. You might check the value of hash_area_size, and maybe increase that, too. HTH, Mike -Original Message- Sent: Thursday, February 14, 2002 4:04 PM To: Multiple recipients of list ORACLE-L Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ERR: ORA-04031 unable to allocate
see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=146599.1 Jared Baker, Barbara [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/14/02 04:03 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:ERR: ORA-04031 unable to allocate Oracle 8.0.5 Solaris 2.6 List: One of our users is getting this error message running a query: ERROR: ORA-04031: unable to allocate 4194304 bytes of shared memory (shared pool,unknown object,cursor work he,KKRH Hash Table) The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.) I'm seeing latch wait this for the sid involved: SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- --- -- 409 latch free address 2147519876 number 59 tries 0 1 pmon timer duration 300 0 0 6 smon timer sleep time 300 failed 0 0 and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site) RESOURCE NSID SID HOLDING WANTINGSECONDS - --- --- -- CU--1595636348-0 409 409 X 0 RT-1-0 4 LGWR X 0 TS-1-8388610 6 SMON SX 55604 According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?) Anyone seen this one before? Thanks for any help! Barb Here's the query: select distinct v.sales_id, a.receiver, a.adno, a.unet, a.vno, a.enddate from advdb.ad a, advdb.pub p, advdb.voluntary_reps v where a.adno = p.adno and a.vno = p.vno and a.receiver = v.name and p.state = 'VAR' and p.vnoflag = 'Y' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ora-04031 errors
I have been getting ora-04031 errors, even though I have much free space within my shared_pool - probably too much free actually. I set up some scripts to run every 10 minutes and as recently as 3 minutes before my ora-04031 errors started again, my shared_pool free memory was 26M based on the query: select sysdate, bytes from v$sgastat where pool = 'shared pool' and name = 'free memory'; and myhit ratio was 99.16. Finally, from Steve Adams site, I ran the following query, and you can see where the errors start. RUN_DATE RECURRENT_CHUNKS TRANSIENT_CHUNKS FLUSHED_CHUNKS PINS_AND_RELEASED ORA_4031_ERRORS LAST_ERROR_SIZE -- - --- --- 28-Jan-2002 2:00:00 296 9712663763 41743160 0 0 28-Jan-2002 2:10:01 344 9612669731 41788495 0 0 28-Jan-2002 2:20:01 421 7262673795 41819190 0 0 28-Jan-2002 2:30:01 473 9412680027 41865752 0 0 28-Jan-2002 2:40:01 271 6252686803 41910898 0 0 28-Jan-2002 2:50:01 232 5962823830 42714680 0 0 28-Jan-2002 3:00:01 242 4203100201 44175166 14168 28-Jan-2002 3:10:01 77 1873364790 4542477211164168 Can someone point out some other things to research? I am thinking that even though I have free space in memory , maybe it is fragmeneted? John Fedock K Line America [EMAIL PROTECTED] www.kline.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fedock, John (KAM.RHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ORA-04031 with Oracle 8.1.7.0.0 on HP-UX 11.0. URGENT
Getting: ORA-04031: unable to allocate 4256 bytes of shared memory (shared pool, unknown object, sga heap, library cache) and ORA-00604: error occurred at recursive SQL level 1 over and over in the alert log. This is a suddenly event on a production database that had been operational at 8.1.6 for a year, and is now at 8.1.7 for a month. Running SilverStream as a front-end, which has about 70 dedicated connections in it's pool (SilverStream does MTS on its own). SilverStream users are unable to login. I'm unable to login sqlplus sys/manager, getting end-of-communication channel. I'm unable to login sqlplus internal, getting already logged in. I'm able to login svrmgrl, but most commands end the session with a not connected. startup gives the expected already started, shutdown first. My questions are (yeah, I know, clairvoiance): - Could anyone who experienced this share solution(s)? - Will a shutdown immediate just hang? - Will a shutdown abort cause harm? (Nothing but once-a-day full export.) - What would you do? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kirsch, Walter J (Northrop Grumman) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031 with Oracle 8.1.7.0.0 on HP-UX 11.0. URGENT
You may be hitting a bug# 1397603 and may have to apply the 8.1.7.1 or 8.1.7.2 patch set. Pl search Metalink for this bug number. - Kirti -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 9:25 AM To: Multiple recipients of list ORACLE-L Getting: ORA-04031: unable to allocate 4256 bytes of shared memory (shared pool, unknown object, sga heap, library cache) and ORA-00604: error occurred at recursive SQL level 1 over and over in the alert log. This is a suddenly event on a production database that had been operational at 8.1.6 for a year, and is now at 8.1.7 for a month. Running SilverStream as a front-end, which has about 70 dedicated connections in it's pool (SilverStream does MTS on its own). SilverStream users are unable to login. I'm unable to login sqlplus sys/manager, getting end-of-communication channel. I'm unable to login sqlplus internal, getting already logged in. I'm able to login svrmgrl, but most commands end the session with a not connected. startup gives the expected already started, shutdown first. My questions are (yeah, I know, clairvoiance): - Could anyone who experienced this share solution(s)? - Will a shutdown immediate just hang? - Will a shutdown abort cause harm? (Nothing but once-a-day full export.) - What would you do? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kirsch, Walter J (Northrop Grumman) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
? ORA-04031
hello ... i got the following error from a full export ... . exporting posttables actions EXP-8: ORACLE error 4031 encountered ORA-04031: unable to allocate 4080 bytes of shared memory (shared pool,DBMS_REPCAT,PL/SQL MPCODE,B AMIMA: Bam Buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1 ORA-06512: at SYS.DBMS_SYS_SQL, line 781 ORA-06512: at SYS.DBMS_SQL, line 316 ORA-06512: at SYS.DBMS_EXPORT_EXTENSION, line 82 ORA-06512: at SYS.DBMS_EXPORT_EXTENSION, line 133 ORA-06512: at line 1 EXP-0: Export terminated unsuccessfully i think it might have something to do with some failed rman backups, and the shared memory segments not being freed up ... but i would appreciate your help in resolving this problem ... thank you in advance ... Nechama Glasrot Oracle DBA Seisint, Inc. 6601 Park of Commerce Blvd. Boca Raton, Florida 33487 [EMAIL PROTECTED] Direct 561.999.3977 Main 561.999.4400 Fax 561.999.4695 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glasrot, Nechama INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ORA-04031
Hi, we are running batch job executing following statement in a loop about 1 million iterations: select sysdate from dual... we are getting error ora-04031 unable to allocate 2400 bytes in shared pool.. and when we check the sys tables it is showing same number of pare call as executions. why oracle is trying to parse this statement every time.. this is the only job running and user connected at this time... Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
Are you using MTS ? If yes, try and increase LARGE_POOL_SIZE Make a FREE long distance call from your PC! http://www.eboom.com/free/ - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 01, 2001 03:25 PM Hi, we are running batch job executing following statement in a loop about 1 million iterations: select sysdate from dual... we are getting error ora-04031 unable to allocate 2400 bytes in shared pool.. and when we check the sys tables it is showing same number of pare call as executions. why oracle is trying to parse this statement every time.. this is the only job running and user connected at this time... Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Nikunj Gupta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031, how to prevent fragmentation
Thanx to all, who answered. Seems to be Bug 1397603 MEMORY LEAKS OBSERVED WHEN RUNNING CONNECTION TESTS as I can observe growing counts for 'state objects'. = apply patchset 8.1.7.2 or = set _db_handles_cached = 0 (only workaround!) Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031, how to prevent fragmentation?
Marc, If you haven't applied the 8.1.7.2 patchset, your ORA-4031 errors could be due to bug 1397603 MEMORY LEAKS OBSERVED WHEN RUNNING CONNECTION TESTS If this is the case, you should see a growth in the memory allocated to state objects: select to_char(sysdate,'mm/dd/yy hh24:mi:ss'),s.* from v$SGASTAT s where name = 'State objects'; Growth is normal after the db is started, but should stabilize after 20-30 minutes of normal activity. If you are seeing growth I would recommend applying the 8.1.7.2 patchet. As a workaround, you can set _db_handles_cached = 0 in the init.ora, just keep in mind that this will increase latch contention so you could experience performance degradation if you already have a problem with latch contention. HTH, -- Anita --- Blum, Marc [EMAIL PROTECTED] wrote: Dear list, we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that? Thanx very much Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon: +49 241 / 9 18 79-33 Fax: +49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031, how to prevent fragmentation
Thanx to all, who answered. Seems to be Bug 1397603 MEMORY LEAKS OBSERVED WHEN RUNNING CONNECTION TESTS as I can observe growing counts for 'state objects'. = apply patchset 8.1.7.2 or = set _db_handles_cached = 0 (only workaround!) Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031, how to prevent fragmentation?
You can either issue an alter system flush shared pool, or increase the value of your shared_pool_size. Another option would be to pin some or your most used/larger packeges in to your shared pool. HTH Mark -Original Message- Sent: Friday, October 26, 2001 10:55 To: Multiple recipients of list ORACLE-L Dear list, we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that? Thanx very much Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031, how to prevent fragmentation?
Hi, *Pin packages into the shared pool *enlarge the shared pool *flush the shared pool at regular intervals *use bind variables i.s.o hardcoded values or a combination of those Jack Blum, Marc [EMAIL PROTECTED]@fatcity.com on 26-10-2001 11:55:18 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Dear list, we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that? Thanx very much Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax: +49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031, how to prevent fragmentation?
Regarding flushing the shared pool... A couple of weeks ago I had hard copies of 2 Metalink docs side by side on my desk and I highlighted paragraphs on each which were completely contradictory. One said flushing did absolutely no good and the other said it could help. In practice it doesn't seem to do much for severe 4031 type fragmentation. Pinning large stuff at db start up can help but if you've got a lot of SQL with literals then consider setting cursor_sharing = TRUE as well. With a small db just enlarging the shared pool may be enough. Of course if you have control over the code then I think using bind variables is still the preferred solution. Steve Orr -Original Message- Sent: Friday, October 26, 2001 6:05 AM To: Multiple recipients of list ORACLE-L You can either issue an alter system flush shared pool, or increase the value of your shared_pool_size. Another option would be to pin some or your most used/larger packeges in to your shared pool. HTH Mark -Original Message- Sent: Friday, October 26, 2001 10:55 To: Multiple recipients of list ORACLE-L Dear list, we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that? Thanx very much Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
need to see a trace file for more info... -Original Message- From: Eca Eca [mailto:[EMAIL PROTECTED]] Sent: Monday, September 24, 2001 7:35 PM To: Multiple recipients of list ORACLE-L Subject: ORA-04031 Friends : I am using 9i and receiving : ORA-04031: unable to allocate 8704 bytes of shared memory (large pool,unknown object,hash-join subh,kllcqas:kllsltba) I have 2gb of memory In init.ora i have large values for pool parameters ... What is happening ? Any idea ? The db_cache_size is around 932 mb shared_pool is around 369 mb Regards Eriovaldo _ Chegou o novo MSN Explorer. Instale já. É gratuito! http://explorer.msn.com.br -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eca Eca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: BELOV INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
Ok. I have solved this problem. I put the parameter large_pool_size with a large value (200mb) and now it is running right... I know that this value is too large, but it solved my problem... We are looking for it ... Regards ... rom: BELOV [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: ORA-04031 Date: Tue, 25 Sep 2001 00:30:21 -0800 need to see a trace file for more info... -Original Message- From: Eca Eca [mailto:[EMAIL PROTECTED]] Sent: Monday, September 24, 2001 7:35 PM To: Multiple recipients of list ORACLE-L Subject: ORA-04031 Friends : I am using 9i and receiving : ORA-04031: unable to allocate 8704 bytes of shared memory (large pool,unknown object,hash-join subh,kllcqas:kllsltba) I have 2gb of memory In init.ora i have large values for pool parameters ... What is happening ? Any idea ? The db_cache_size is around 932 mb shared_pool is around 369 mb Regards Eriovaldo _ Chegou o novo MSN Explorer. Instale já. É gratuito! http://explorer.msn.com.br -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eca Eca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: BELOV INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Chegou o novo MSN Explorer. Instale já. É gratuito! http://explorer.msn.com.br -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eca Eca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ORA-04031
Friends : I am using 9i and receiving : ORA-04031: unable to allocate 8704 bytes of shared memory (large pool,unknown object,hash-join subh,kllcqas:kllsltba) I have 2gb of memory In init.ora i have large values for pool parameters ... What is happening ? Any idea ? The db_cache_size is around 932 mb shared_pool is around 369 mb Regards Eriovaldo _ Chegou o novo MSN Explorer. Instale já. É gratuito! http://explorer.msn.com.br -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eca Eca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
According to MetaLink Error: ORA 4031 Text: unable to allocate %s bytes of shared memory (%s,%s,%s) --- Cause: More shared memory is needed than was allocated in the shared pool. Action: Either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the init.ora parameter shared_pool_size. Rick -Original Message- Sent: Monday, September 24, 2001 11:35 AM To: Multiple recipients of list ORACLE-L Friends : I am using 9i and receiving : ORA-04031: unable to allocate 8704 bytes of shared memory (large pool,unknown object,hash-join subh,kllcqas:kllsltba) I have 2gb of memory In init.ora i have large values for pool parameters ... What is happening ? Any idea ? The db_cache_size is around 932 mb shared_pool is around 369 mb Regards Eriovaldo _ Chegou o novo MSN Explorer. Instale já. É gratuito! http://explorer.msn.com.br -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eca Eca INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Why am I not getting ORA-04031 errors in my alert log?
We were seeing ORA-04031 errors in our log files from exports and rman backups on a recently migrated 8.1.7 database on Sun Solaris 2.6. Our end users of our web-based applications were also seeing ORA-04031 errors upon executing their application. We increased shared pool size and bounced the database. We are also investigating a possible 8.1.7 bug related to ORA-04031 errors. My question is this: Why didn't we see any ORA-04031 errors in the alert log? None registered in our alert log throughout that period. Anybody know what the deal is? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ORA-04031
I have a small database with only about 6-10 users. I am getting ORA-04031 errors indicating shared_pool problems. Can anyone give me a suggestion on how to size the shared pool? My current parameters are: shared_pool _reserve_size 209715 shared_pool_size 4194304 large_pool_size 614400 Ron Smith Database Administration [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 31, 2001 12:57 PM To: Multiple recipients of list ORACLE-L See Note:62427.1, 2Gb or Not 2Gb - File limits in Oracle http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOT http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data base_id=NOTp_id=62427.1 p_id=62427.1 -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Thursday, May 31, 2001 9:56 AM To: Multiple recipients of list ORACLE-L Depends is the standard answer. Oracle can handle files over 2g, some unixes can, some unixes cannot, some say they can but cannot etc etc... To compound things, in some versions, Oracle will let the file go beyond 2g, only then to complain because the unix won't let Oracle get to the bits after 2G - thus corrupt db. Unless you're on raw, I'd recommend a ceiling of 2g on any datafile - just to be safe hth connor --- Smith, Ron L. [EMAIL PROTECTED] wrote: I have taken over an Oracle database that is setup with autoextend on the tablespaces. Can anyone tell me what happens when the datafiles extend beyond 2G on Unix? . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Connor McDonald http://www.oracledba.co.uk http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk http://www.oradba.freeserve.co.uk ) Some days you're the pigeon, some days you're the statue Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
RS - You'll get alot of posts on this, but. Basically multiply your pool by about 5x ( at least! ) along with the reserve size. Set the min_alloc (as/if relevant in yer version to about 16K) If you are doing parallel execution and or RMAN stuff, grow the large pool, too. Oh, hell, you might as well pin some stuff while you're at it. hth, Ross || -Original Message- || From: Smith, Ron L. [mailto:[EMAIL PROTECTED]] || Sent: Thursday, June 07, 2001 10:03 AM || To: Multiple recipients of list ORACLE-L || Subject: ORA-04031 || || || I have a small database with only about 6-10 users. I am || getting ORA-04031 || errors indicating shared_pool problems. Can anyone give me || a suggestion on || how to size the shared pool? || || My current parameters are: || shared_pool _reserve_size 209715 || shared_pool_size 4194304 || large_pool_size 614400 || || Ron Smith || Database Administration || [EMAIL PROTECTED] || || -Original Message- || Sent: Thursday, May 31, 2001 12:57 PM || To: Multiple recipients of list ORACLE-L || || || || See Note:62427.1, 2Gb or Not 2Gb - File limits in Oracle || http://metalink.oracle.com/metalink/plsql/ml2_documents.showD || ocument?p_datab || ase_id=NOT || http://metalink.oracle.com/metalink/plsql/ml2_documents.show || Document?p_data || base_id=NOTp_id=62427.1 p_id=62427.1 || || || -Original Message- || mailto:[EMAIL PROTECTED] ] || Sent: Thursday, May 31, 2001 9:56 AM || To: Multiple recipients of list ORACLE-L || || || Depends is the standard answer. || || Oracle can handle files over 2g, some unixes can, some || unixes cannot, some say they can but cannot etc etc... || || To compound things, in some versions, Oracle will let || the file go beyond 2g, only then to complain because || the unix won't let Oracle get to the bits after 2G - || thus corrupt db. || || Unless you're on raw, I'd recommend a ceiling of 2g on || any datafile - just to be safe || || hth || connor || || --- Smith, Ron L. [EMAIL PROTECTED] wrote: I have || taken over an Oracle database that is setup || with autoextend on the || tablespaces. Can anyone tell me what happens when || the datafiles extend || beyond 2G on Unix? || || . || || -- || Please see the official ORACLE-L FAQ: || http://www.orafaq.com http://www.orafaq.com || -- || Author: Smith, Ron L. ||INET: [EMAIL PROTECTED] || || Fat City Network Services-- (858) 538-5051 FAX: || (858) 538-5051 || San Diego, California-- Public Internet || access / Mailing Lists || || || 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). || || || = || Connor McDonald || http://www.oracledba.co.uk http://www.oracledba.co.uk || (mirrored at || http://www.oradba.freeserve.co.uk || http://www.oradba.freeserve.co.uk ) || || Some days you're the pigeon, some days you're the statue || || || Do You Yahoo!? || Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk || http://mail.yahoo.co.uk || or your free @yahoo.ie address at http://mail.yahoo.ie || http://mail.yahoo.ie || -- || Please see the official ORACLE-L FAQ: http://www.orafaq.com || http://www.orafaq.com || -- || Author: =?iso-8859-1?q?Connor=20McDonald?= || INET: [EMAIL PROTECTED] || || Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 || San Diego, California-- Public Internet access / || Mailing Lists || || 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.com || -- || Author: Smith, Ron L. || INET: [EMAIL PROTECTED] || || Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 || San Diego, California-- Public Internet access / || Mailing Lists || || 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
Re: ORA-04031
Also pin your most frequent used/loaded objects at database startup time to avoid this problem... You may use following query as a start to monitor such objects.You may change where clause as per your environment. I normally track such objects which have executions 1000 regardless of size. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 26 Apr 2001 09:32:02 -0800 Increase it... [EMAIL PROTECTED] 04/26/01 12:00PM Hi All After upgrading from 8.1.6.0. to 8.1.6.3 I am getting the following error repeatedly. ORA-04031: unable to allocate 4096 bytes of shared memory(shared pool,TRIG_OWSSUBS,PL/SQL MPCODE,BAMIMA: Bam Buffer) Also this error appears in the Oracle Alert log (not sure wether its connected) Errors in file /u01/app/oracle/admin/ukdb/udump/ukdb_ora_8432.trc: ORA-07445: exception encountered: core dump [kkects()+64] [SIGSEGV] [Address not mapped to object] [8] [] [] This is my shared pool size at the moment. shared_pool_size = 52428800 TIA Saj -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ORA-04031
Hi All After upgrading from 8.1.6.0. to 8.1.6.3 I am getting the following error repeatedly. ORA-04031: unable to allocate 4096 bytes of shared memory(shared pool,TRIG_OWSSUBS,PL/SQL MPCODE,BAMIMA: Bam Buffer) Also this error appears in the Oracle Alert log (not sure wether its connected) Errors in file /u01/app/oracle/admin/ukdb/udump/ukdb_ora_8432.trc: ORA-07445: exception encountered: core dump [kkects()+64] [SIGSEGV] [Address not mapped to object] [8] [] [] This is my shared pool size at the moment. shared_pool_size = 52428800 TIA Saj -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ORA-04031
Increase it... [EMAIL PROTECTED] 04/26/01 12:00PM Hi All After upgrading from 8.1.6.0. to 8.1.6.3 I am getting the following error repeatedly. ORA-04031: unable to allocate 4096 bytes of shared memory(shared pool,TRIG_OWSSUBS,PL/SQL MPCODE,BAMIMA: Bam Buffer) Also this error appears in the Oracle Alert log (not sure wether its connected) Errors in file /u01/app/oracle/admin/ukdb/udump/ukdb_ora_8432.trc: ORA-07445: exception encountered: core dump [kkects()+64] [SIGSEGV] [Address not mapped to object] [8] [] [] This is my shared pool size at the moment. shared_pool_size = 52428800 TIA Saj -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
