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
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
-
Use bind variables in application SQL so statements are shared.
SELECT * FROM orders WHERE customer_id = :customer_id;
-
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;
-
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
ORA-01652unable to extend temp segment701SQL Server: Insufficient memory in resource poolSQLITE_NOMEMSQLite: out of memory
More space, memory and limits errors
ORA-01653unable to extend table in tablespaceORA-01950no privileges on tablespaceORA-01555snapshot too oldORA-30036unable to extend segment in undo tablespaceORA-01000maximum open cursors exceededORA-00018maximum number of sessions exceededORA-00020maximum number of processes exceededORA-00257archiver error
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.