ORA-04031 (See ORA-04031.ora-code.com): unable o allocate 40048 bytes of shared memory ( "shared
pool ", "SELECT : SASOFPD.PD_DT, : ... ", "sqa area ", "text : kpp_extend ")
:-4031
when trying to run a monstrous 26.1kb query. Yes that is the amount of text.
The one query involves 9 case statements, some nested in others and several
involving more than 50 when clauses. It also has 8 sum(), over 200 to_date()
and a group by clause... Don 't ask.
My guess is that we are running out of library_cache when this beast is
parsed out. Sound reasonable?
Also, does anyone know of a way to determine how much
library_cache/shared_pool a session is using.
-- ---- ---- ---- ---- ---- ---- -----
Bob Lofstrand mailto:blofstrand@(protected)
Oracle Database Administrator www.csedge.com
Corporate Systems Inc. 806-337-3906
"There is no silver bullet. " -Frederick P. Brooks. The Mythical Man-Month.
The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies.
Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD >
<META HTTP-EQUIV= "Content-Type " CONTENT= "text/html; charset=iso-8859-1 " >
<META content= "MSHTML 5.50.4930.1700 " name=GENERATOR > </HEAD >
<BODY >
<DIV > <FONT face=Arial size=2 > <SPAN class=280161422-25022004 >We get this
error: </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial size=2 > <SPAN
class=280161422-25022004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial size=2 > <SPAN class=280161422-25022004 >ORA-04031 (See ORA-04031.ora-code.com): unable o
allocate 40048 bytes of shared memory ( "shared <BR >pool ", "SELECT :
SASOFPD.PD_DT, : ... ", "sqa area ", "text :
kpp_extend ") <BR >:-4031 </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial size=2 > <SPAN
class=280161422-25022004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial size=2 > <SPAN class=280161422-25022004 >when trying to run a
monstrous 26.1kb query. Yes that is the amount of text. The one query involves 9
case statements, some nested in others and several involving more than 50 when
clauses. It also has 8 sum(), over 200 to_date() and a group by clause... Don 't
ask. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial size=2 > <SPAN
class=280161422-25022004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial size=2 > <SPAN class=280161422-25022004 >My guess is that we
are running out of library_cache when this beast is parsed out. Sound
reasonable? </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial size=2 > <SPAN
class=280161422-25022004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial size=2 > <SPAN class=280161422-25022004 >Also, does anyone
know of a way to determine how much library_cache/shared_pool a session is
using. </SPAN > </FONT > </DIV >
<DIV class=Section1 >
<P > <SPAN
style= "FONT-SIZE: 10pt; FONT-FAMILY: Arial " >-- ---- ---- ---- ---- ---- ---- ----- </SPAN >
<BR > <SPAN style= "FONT-SIZE: 10pt; FONT-FAMILY: Arial " >Bob Lofstrand
<table > <tr > <td bgcolor=#ffffff > <font color=#000000 > <pre >The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies.
Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.
</pre > </font > </td > </tr > </table >
