ORA-04031 unable to allocate string bytes of shared memory ("string","string","string","string") Cause More shared memory is needed than was allocated in the shared pool. Action If the shared pool is out of memory, 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 initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.
Action If the shared pool is out of memory, 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 initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.
调整如下的参数:SHARED_POOL_SIZE,
SHARED_POOL_RESERVED_SIZE,
SHARED_POOL_RESERVED_MIN_ALLOC.首先判定是否ORA-04031 错误是由共享池保留空间中的库高速缓冲的碎片产生的。提交下的查 询:SELECT free_space, avg_free_size, used_space, avg_used_size,
request_failures, last_failure_size
FROM v$shared_pool_reserved;如果:REQUEST_FAILURES > 0 并且
LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC那么ORA-04031 错误就是因为共享池保留空间缺少连续空间所致。
要解决这个问题,可以考虑加大SHARED_POOL_RESERVED_MIN_ALLOC 来降低缓冲进共 享池保留空间的对
象数目,并增大 SHARED_POOL_RESERVED_SIZE 和SHARED_POOL_SIZE 来加大共享池保留空间的可用
内存。如果:
REQUEST_FAILURES > 0 并且
LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC或者REQUEST_FAILURES 等于0 并且
LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC那么是因为在库高速缓冲缺少连续空间导致ORA-04031 错误。第一步应该考虑降低SHARED_POOL_RESERVED_MIN_ALLOC 以放入更多的对象到共享池
保留空间中并且加大SHARED_POOL_SIZE。