// Oracle error reference

ORA-04031: unable to allocate bytes of shared memory

Oracle couldn't find a large enough piece of memory in the shared pool or another SGA pool, often from heavy hard parsing of SQL without bind variables.

Error code
ORA-04031
Category
Space, memory and limits
Error message
unable to allocate string bytes of shared memory ("string","string","string","string")

What it means

ORA-04031 means an allocation in the SGA (usually the shared pool, sometimes the large or Java pool) failed. The message names the pool and what was being allocated. Shared pool fragmentation from many unique SQL statements, typically SQL built with literal values instead of bind variables, is a classic cause.

Newer releases word it as out of shared memory in heap_name.

Common causes

  • Applications concatenating values into SQL text, so every statement is new and must be hard parsed and cached.
  • A shared pool (or SGA) sized too small for the workload.
  • Large allocations for parallel query or RMAN in a too-small large pool.

How to fix it

  1. Use bind variables in application SQL so statements are shared.

    SELECT * FROM orders WHERE customer_id = :customer_id;
  2. Check pool sizes and free memory, and enlarge the SGA if needed.

    SELECT pool, name, bytes FROM v$sgastat WHERE name = 'free memory';
    ALTER SYSTEM SET sga_target = 8G SCOPE = SPFILE;
  3. As a short-term relief only, flushing the shared pool frees fragments, at the cost of re-parsing everything.

    ALTER SYSTEM FLUSH SHARED_POOL;

Related errors

More space, memory and limits errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.