ORA-04031 on Oracle 8.1.7.0

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

ORA-04031 on Oracle 8.1.7.0

ORA-04031 on Oracle 8.1.7.0

2006-06-28 - By Paula Stankus

Oracle Version 8.1.7.0
Solaris 2.8

We ran into an issue using database links and mv's. It seemed to work to
recreate instead of refreshing mv's but I am thinking that that might be
causing an issue with the shared pool as now we are getting

ORA-04031 (See ORA-04031.ora-code.com): unable to allocate 17732 bytes of shared
memory ("shared pool","MV_AGE_GENDER","library cache","summary text")

MyDFSMV -
ORA-04031 (See ORA-04031.ora-code.com): unable to allocate 4096 bytes of shared
memory ("shared pool","begin sys.dbms_snapshot_ut...","PL/SQL MPCODE",
"BAMIMA:
Bam Buffer")

I ran the following healthcheck finding a note on metalink

DOC>* *
DOC>* TITLE : Shared Pool Estimation *
DOC>* CATEGORY : Information, Utility *
DOC>* SUBJECT AREA : Shared Pool *
DOC>* DESCRIPTION : Estimates shared pool utilization *
DOC>* based on current database usage. This should be *
DOC>* run during peak operation, after all stored *
DOC>* objects i.e. packages, views have been loaded. *
DOC>* NOTE: Modified to work with later versions 4/11/06 *
DOC>* *
DOC>********************************************************/
Obj mem: 32640933 bytes
Shared sql: 1236627 bytes
Cursors: 22875 bytes
Free memory: 13015852 bytes (12.41MB)
Shared pool utilization (total): 40680522 bytes (38.8MB)
Shared pool allocation (actual): 52428800
bytes (50MB)
Percentage Utilized: 78%
PL/SQL procedure successfully completed.

Anyone know of any issues with mv's and shared pool or the way they are
working and if I should just increase the shared pool size - I am about to do
that.

-- ---- ---- ---- ---- ---- -----
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail Beta.
<div class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial">Oracle Version 8.1.7.0</SPAN></FONT></div> <div class=MsoNormal
><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"
>Solaris 2.8</SPAN></FONT></div> <div class=MsoNormal><FONT face=Arial size=2>
<SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </div>
<div class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial">We ran into an issue using database links and mv's.  It
seemed to work to recreate instead of refreshing mv's but I am thinking that
that might be causing an issue with the shared pool as now we are getting <
/SPAN></FONT></div> <div class=MsoNormal><FONT face=Arial size=2><SPAN style=
"FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </div> <div class
=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY:
Arial">ORA-04031 (See ORA-04031.ora-code.com): unable to allocate 17732 bytes of shared
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o
:p></o:p></SPAN></FONT></div> <div class=MsoNormal><FONT face=Arial size=2>
<SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">memory ("shared pool","MV_AGE
_GENDER","library cache","summary text")<o:p></o:p></SPAN></FONT></div> <div
class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></div> <div class=MsoNormal>
<FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"
>MyDFSMV - <o:p></o:p></SPAN></FONT></div> <div class=MsoNormal><FONT face
=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">ORA-04031 (See ORA-04031.ora-code.com):
unable to allocate 4096 bytes of shared<o:p></o:p></SPAN></FONT></div> <div
class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial">memory ("shared pool","begin   sys.dbms_snapshot_ut...
","PL/SQL MPCODE","BAMIMA:<o:p></o:p></SPAN></FONT></div> <div
class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial">Bam Buffer")</SPAN></FONT></div> <div class=MsoNormal><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN><
/FONT> </div> <div class=MsoNormal><FONT face=Arial size=2><SPAN style=
"FONT-SIZE: 10pt; FONT-FAMILY: Arial">I ran the following healthcheck finding a
note on metalink</SPAN></FONT></div> <div class=MsoNormal><FONT face=Arial
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> <
/div> <div class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt
; FONT-FAMILY: Arial">DOC>*        
             
             
             
        *<BR>DOC>*
TITLE        : Shared Pool Estimation 
             
   *<BR>DOC>* CATEGORY     : Information,
Utility            
       *<BR>DOC>* SUBJECT AREA : Shared Pool
             
             
  *<BR>DOC>* DESCRIPTION  : Estimates shared pool utilization 
     *<BR>DOC>*  based on current database usage.
This should be      *<BR>DOC>*  run during
peak operation, after all stored       &nbsp
;  *<BR>DOC>*  objects i.e. packages, views have been loaded. 
      *<BR>DOC>*
NOTE:  Modified to work with later versions 4/11/06   *<BR>DOC
>*            
             
             
             
    *<BR>DOC>*************************************************
*******/<BR>Obj mem:  32640933 bytes<BR>Shared sql:  1236627 bytes<BR
>Cursors:  22875 bytes<BR>Free memory: 13015852 bytes (12.41MB)<BR>Shared
pool utilization (total):  40680522 bytes (38.8MB)<BR>Shared pool
allocation (actual):  52428800<BR>bytes (50MB)<BR>Percentage Utilized:
  78%</SPAN></FONT></div> <div class=MsoNormal><FONT face=Arial size=2>
<SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">PL/SQL procedure successfully
completed.<BR></div> <div><o:p></o:p> </div>
<div><o:p>Anyone know of any issues with mv's and shared pool or the way they
are working and if I should just increase the shared pool size - I am about to
do that.  </o:p></SPAN></FONT></div><p> 
<hr size=1>Do you Yahoo!?<br> Everyone is raving about the <a href="http://us
.rd.yahoo.com/evt=40791/*http://advision.webevents.yahoo.com/handraisers"> all
-new Yahoo! Mail Beta.</a>