ORA-04031: unable to allocate shared memory

ORA-04031: unable to allocate N bytes of shared memory ("shared pool","...","...","...")

A memory pool in the SGA, usually the shared pool, could not provide a contiguous chunk. Common causes are SQL without bind variables, a pool that is too small, or fragmentation. Use bind variables first; flushing the pool is only a temporary relief.

What ORA-04031 means

The shared pool stores parsed SQL, PL/SQL and dictionary data. If an application sends thousands of statements that differ only in their literal values, each one takes memory of its own, and the pool fragments until a request can no longer be met. The message names the pool: shared pool, large pool, java pool or streams pool.

Common causes of ORA-04031

Application SQL built with literals instead of bind variables.
SHARED_POOL_SIZE or SGA_TARGET set too low.
Fragmentation after long uptime, or a very large object being loaded.
A known bug that a release update fixes, in some versions.

How to fix ORA-04031

Look at V$SGASTAT and V$SGA_RESIZE_OPS to see which component is under pressure.
Find literal SQL: group V$SQLAREA by FORCE_MATCHING_SIGNATURE.
Fix the application with bind variables. CURSOR_SHARING = FORCE is a temporary workaround that should be tested first.
Increase the SGA if it is truly undersized. ALTER SYSTEM FLUSH SHARED_POOL gives short-lived relief, and it causes a burst of hard parsing.

ORA-04031 example

Statements that differ only in literals:

SELECT force_matching_signature, COUNT(*) AS variants, MIN(sql_text) AS example
  FROM v$sqlarea
 WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(*) > 50
 ORDER BY variants DESC;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-04031. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.