ORA-04031 Error Analysis and resolution

ORA-04031 Error Analysis and resolution

The query v$shared_pool_reserved view can determine the cause of the shared pool problem:

Sql>select Free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size from V$shared_ pool_reserved;

Free_space avg_free_size used_space avg_used_size request_failures last_failure_size

---------- ------------- ---------- ------------- ---------------- -----------------

2685760 167860 0 0 0 0

If Request_failures>0 and Last_failure_size>shared_pool_reserved_min_alloc, then 4031 may be caused by a shared pool including space missing contiguous space Consider increasing the shared_pool_reserved_min_alloc and increasing shared_pool_reserved_size and shared_pool_size to increase the available memory for the shared pool reserved space.

If Request_failures>0 and Last_failure_size<shared_pool_reserved_min_alloc, or request_failures=0 and last_failure_ Size<shared_pool_reserved_min_alloc, you should consider lowering the shared_pool_reserved_min_alloc.

Query for Shared_pool_reserved_min_alloc parameter values:

Sql>

Column name format A35

Column value Format 9999999

Column IsDefault format A5

Column Ismod format A5

Column Isadj format A5

Select

X.KSPPINM name,

Y.KSPPSTVL value,

Y.KSPPSTDF IsDefault,

Decode (Bitand (y.ksppstvf,7), 1, ' Modified ', 4, ' System_mod ', ' false ') Ismod,

Decode (Bitand (y.ksppstvf,2), 2, ' true ', ' false ') Isadj

From

Sys.x$ksppi x,

SYS.X$KSPPCV y

where

x.inst_id = Userenv (' instance ') and

y.inst_id = Userenv (' instance ') and

X.indx = Y.indx

and x.ksppinm like ' _shared_pool% '---/**_% query all implied parameters '

/

NAME VALUE ISDEF ismod Isadj

----------------------------------- ------- ----- ----- ---------------------------------------------------

_shared_pool_reserved_pct 5 TRUE False

_shared_pool_reserved_min_alloc 4400 TRUE False

Experience: Set _shared_pool_reserved_min_alloc=4100, do not recommend lower set, generally between 4100~4400.