Most commonly this error comes because of smaller SHARED POOL - which is also mentioned with the error message as follows
Other Memory Structures
Other than shared pool, you may also face same ORA-4031 if sufficient free memory is not available in other POOLs i.e. JAVA pool, Streams pool or Large pool. In either case, have a close look at the memory requirements for each of the pool for which you are receiving this error message.
ERROR at line 1:
ORA-04031: unable to allocate 4018 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","kglsim heap")
ORA-04031: unable to allocate 4018 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","kglsim heap")
Reason
When an object(PL/SQL, SQL) is loaded in the shared pool, oracle needs contiguous free space in the pool to load the whole object, and if it cannot find a free chunk of space to load the whole object in contiguous memory blocks, it throws ORA-4031 error. The reason for this error is fragmentation of shared pool where it may have some free space available, but, free space is not contiguous to load the object in a single bigger free chunk of memory.
It would be pertinent to mention that "free space" does not necessarily mean "free/unused space" in memory. Free space actually means the space either free (never used), or space available to be reused as listed in LRU (Least Recently Used) list. LRU list is maintained to help oracle process to use the memory blocks which are not "in use" for a longer of period as compared to other objects in the pool.
It would be pertinent to mention that "free space" does not necessarily mean "free/unused space" in memory. Free space actually means the space either free (never used), or space available to be reused as listed in LRU (Least Recently Used) list. LRU list is maintained to help oracle process to use the memory blocks which are not "in use" for a longer of period as compared to other objects in the pool.
Temporary Solution
Issue following statement to free up the whole shared pool.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
Permanent Solution
Increase the amount of memory used by shared pool by setting either of the following (based on memory settings you are using)
SHARED_POOL_SIZE
SGA_TARGET
MEMORY_TARGET
Other Memory Structures
Other than shared pool, you may also face same ORA-4031 if sufficient free memory is not available in other POOLs i.e. JAVA pool, Streams pool or Large pool. In either case, have a close look at the memory requirements for each of the pool for which you are receiving this error message.
No comments:
Post a Comment