ORA-04031: unable to allocate bytes of shared memory (shared pool)

ORA-04031 fires when a session needs a chunk of shared pool memory and Oracle cannot find a single contiguous free region large enough. The full message lists four values: bytes requested, pool name, allocation type, and heap name. A typical first sighting: ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool","unknown object","sga heap","row cache buffers"). It can also fire against the large pool, java pool, or streams pool.

The trap is treating ORA-04031 as a capacity problem. It is often fragmentation: the pool has free bytes in aggregate but no single contiguous free chunk that fits the allocation. The most common driver is a parse storm. An application sending literal SQL forces Oracle to hard-parse thousands of near-identical statements, each consuming its own library cache chunk. Over time the shared pool fragments and even small allocations start failing.

This article covers the diagnosis flow that separates an undersized pool from fragmentation, the queries that confirm each cause, and the operator mistakes, most notably ALTER SYSTEM FLUSH SHARED_POOL, that extend an incident rather than ending one. For the broader SGA model see how Oracle Database actually works in production.

What this means

The shared pool holds the library cache (parsed SQL and PL/SQL), the data dictionary cache, the result cache, and control structures. It is shared by all sessions, and most allocations inside it are not relocatable. When Oracle loads a new cursor, dictionary entry, or PL/SQL object, it carves a chunk from the shared pool. When a cursor ages out, that chunk returns to a freelist.

The shared pool is not one bucket. It is divided into subheaps, and within each subheap memory is allocated in chunks. A single allocation must be satisfied from a single contiguous free chunk. A shared pool can have hundreds of megabytes free in aggregate and still raise ORA-04031 if the largest free chunk is smaller than the request. The reserved pool (controlled by _shared_pool_reserved_pct, default 5%, servicing allocations above approximately 4400 bytes) exists to reduce this risk for larger allocations, but it does not eliminate it.

When ORA-04031 fires, the failing statement fails at the parse stage but the session survives. Severe cases can prevent new sessions from being serviced, because authentication and session setup also allocate from the shared pool.

ORA-04031 in any pool other than “shared pool” usually points at a feature-specific consumer. RMAN uses the large pool for I/O buffers, shared server uses it for session memory (UGA), and parallel query uses it for message buffers. The diagnostic approach is the same in shape: identify the dominant consumer and resize the affected pool.

Common causes

Quick checks

Run these read-only queries from SQL*Plus or any client with V$ view access. None of them change database state.

-- Recent ORA-04031 entries in the alert log (12c+ XML view)
SELECT originating_timestamp, message_text
FROM V$DIAG_ALERT_EXT
WHERE message_text LIKE '%ORA-04031%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

-- Shared pool free memory
SELECT POOL, NAME, BYTES/1048576 AS mb
FROM V$SGASTAT
WHERE POOL = 'shared pool' AND NAME = 'free memory';

-- Reserved pool health: REQUEST_FAILURES > 0 confirms fragmentation
SELECT FREE_SPACE/1048576 AS free_mb,
       AVG_FREE_SIZE, FREE_COUNT, MAX_FREE_SIZE,
       REQUEST_MISSES, REQUEST_FAILURES,
       LAST_FAILURE_SIZE
FROM V$SHARED_POOL_RESERVED;

-- Hard parse rate trend (sample twice, divide delta by interval seconds)
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('parse count (total)', 'parse count (hard)', 'parse count (failures)');

-- Top SQL by executions = 1, sorted by recent activity (literal SQL fingerprint)
SELECT SQL_ID, CHILD_NUMBER, EXECUTIONS, LAST_ACTIVE_TIME,
       SUBSTR(SQL_TEXT, 1, 120) AS sql_text
FROM V$SQL
WHERE EXECUTIONS = 1
ORDER BY LAST_ACTIVE_TIME DESC
FETCH FIRST 30 ROWS ONLY;

-- High child-cursor-count SQL (bind peeking or adaptive cursor sharing)
SELECT SQL_ID, COUNT(*) AS child_cursors
FROM V$SQL
GROUP BY SQL_ID
HAVING COUNT(*) > 10
ORDER BY child_cursors DESC
FETCH FIRST 20 ROWS ONLY;

-- ASMM resize history (recent component operations)
SELECT COMPONENT, OPER_TYPE, OPER_MODE, PARAMETER,
       INITIAL_SIZE/1048576 AS initial_mb,
       FINAL_SIZE/1048576 AS final_mb,
       STATUS, START_TIME, END_TIME
FROM V$SGA_RESIZE_OPS
ORDER BY START_TIME DESC
FETCH FIRST 30 ROWS ONLY;

How to diagnose it

The first question is whether the pool is genuinely out of memory or merely fragmented. The answer determines whether you resize or eliminate the source of fragmentation.

flowchart TD
  A[ORA-04031 in alert log] --> B{V$SGASTAT
shared pool free < 5%?}
  B -->|Yes| C[Pool genuinely pressured]
  B -->|No, errors persist| D[Fragmentation or reserved pool pressure]
  C --> E{Hard parse rate high?
V$SYSSTAT parse count hard}
  E -->|Yes| F[Literal SQL / parse storm]
  E -->|No| G[Check ASMM resize thrash
and large PL/SQL]
  D --> H{V$SHARED_POOL_RESERVED
REQUEST_FAILURES > 0?}
  H -->|Yes| I[Large allocations failing
reserved pool undersized]
  H -->|No| J[Child cursor explosion
check V$SQL]

1. Confirm the error scope. Pull the last 20 ORA-04031 entries from the alert log and note which pool is named. “shared pool” points at SQL parsing. “large pool” points at RMAN, shared server, or parallel query. “java pool” or “streams pool” are feature-specific.
2. Measure actual free memory. If V$SGASTAT shows shared pool free memory below 5% of pool size, the pool is genuinely pressured. If free memory is well above 5% but ORA-04031 still fires, the cause is fragmentation or reserved pool exhaustion, not overall capacity.
3. Check the hard parse rate. Sample parse count (hard) twice, 60 seconds apart. Anything above 100 hard parses per second sustained is concerning on an OLTP system. Compare to parse count (total): a healthy OLTP system keeps the hard parse ratio below 1%.
4. Check the reserved pool. V$SHARED_POOL_RESERVED.REQUEST_FAILURES should be zero on a healthy system. Any non-zero value means even the reserved pool cannot satisfy large allocations. LAST_FAILURE_SIZE tells you the allocation that could not be served.
5. Find the literal SQL. Sort V$SQL by EXECUTIONS = 1 and recent LAST_ACTIVE_TIME. If you see dozens of statements that differ only in literal values, the application is not using bind variables. This is the most common root cause.
6. Check child cursor counts. Group V$SQL by SQL_ID and count children. More than 10 child cursors on a single parent usually indicates bind variable peeking combined with adaptive cursor sharing. More than 100 indicates a cursor sharing problem that will eventually fragment the pool. Query V$SQL_SHARED_CURSOR for the reason code on each child.
7. Inspect ASMM behavior. If SGA_TARGET is set, look at V$SGA_RESIZE_OPS for oscillating component resizes (buffer cache grows, shared pool shrinks, then the reverse). This means SGA_TARGET is too small and ASMM is trading memory between components that both need it.

Metrics and signals to monitor

Fixes

Fix the application: bind variables

The permanent fix for parse-storm-driven ORA-04031 is to change the application to use bind variables. ORM-generated SQL, dynamic SQL builders, and string-concatenated queries are the usual offenders. No database-side parameter will fully substitute for this work.

Bridge fix: CURSOR_SHARING = FORCE

ALTER SYSTEM SET CURSOR_SHARING = FORCE tells Oracle to rewrite literals in incoming SQL with system-generated bind variables. It is documented as a bridge, not a permanent setting. Side effects include plan quality regressions, because the optimizer sees a representative bind value instead of the literal. Test against critical SQL before applying permanently in production.

Do not use CURSOR_SHARING = SIMILAR. That value was deprecated in 11gR2 and is no longer a documented value in current releases — the reference lists only EXACT and FORCE.

Resize the shared pool

If V$SGASTAT shows the pool genuinely undersized, increase the affected parameter:

• With ASMM (SGA_TARGET set): increase SGA_TARGET, or set SHARED_POOL_SIZE to a non-zero floor to prevent ASMM from shrinking it.
• Without ASMM: increase SHARED_POOL_SIZE directly.
• With AMM (MEMORY_TARGET set): increase MEMORY_TARGET or SGA_TARGET.

SHARED_POOL_SIZE and SGA_TARGET are dynamic for upward changes in current releases. AMM is generally not recommended on Linux because it uses /dev/shm instead of hugepages.

Tune SHARED_POOL_RESERVED_SIZE

If fragmentation is the problem and REQUEST_FAILURES is non-zero, increasing SHARED_POOL_RESERVED_SIZE gives Oracle more room for large allocations. The default is approximately 5% of the shared pool. This parameter is static and requires an instance restart to change. A reasonable upper bound is 10% of the shared pool, but verify against your workload.

Pin large packages with DBMS_SHARED_POOL.KEEP

Frequently-executed large packages such as STANDARD, DBMS_STANDARD, DBMS_STATS, and application-critical packages can be pinned in the shared pool at startup using DBMS_SHARED_POOL.KEEP. Pinned objects are not aged out, reducing fragmentation from large-object reloads. Add the KEEP calls to a startup trigger or post-startup script.

Investigate child cursor explosion

If a single SQL_ID has many child cursors, look at V$SQL_SHARED_CURSOR for the reason code. Common causes include bind variable peeking combined with adaptive cursor sharing, varying session optimizer settings, and statistics changes. The fix is usually to make the optimizer environment consistent across sessions.

Large pool specific

For ORA-04031 against the large pool, the consumers are RMAN, shared server, or parallel query. Increase LARGE_POOL_SIZE or, under ASMM, increase SGA_TARGET. If the large pool error appears only during an RMAN window with block change tracking enabled, the CTWR process may be the consumer (its buffers are allocated in the large pool).

Why FLUSH SHARED_POOL is the wrong reflex

ALTER SYSTEM FLUSH SHARED_POOL is the most overused wrong answer to ORA-04031. Flushing clears the library cache, which means every subsequent SQL statement must be hard-parsed. On a busy OLTP system this produces a hard-parse storm that drives CPU up and can deepen the very fragmentation you were trying to relieve. The shared pool refills with the same workload within minutes, so any relief is temporary.

The only defensible use of FLUSH SHARED_POOL is a one-time intervention for documented shared pool corruption, with the application team aware that a hard-parse spike will follow. It is not a recurring maintenance task. If you find it on a cron job or in a runbook, remove it and chase the underlying cause.

Prevention

• Fix literal SQL at the source. Code review and ORM configuration changes are usually required. This is the only durable fix for parse-storm-driven ORA-04031.
• Alert on shared pool free memory below 5%. Trend the value over time, not just current readings.
• Alert on a hard parse ratio above 1% sustained, or above 100 hard parses per second.
• Alert on any REQUEST_FAILURES in V$SHARED_POOL_RESERVED. Even one means the pool is fragmented enough to fail a large allocation.
• Pin critical packages with DBMS_SHARED_POOL.KEEP in a startup script.
• Size SGA_TARGET conservatively. If ASMM is oscillating between buffer cache and shared pool, raise SGA_TARGET or set a SHARED_POOL_SIZE floor.
• Track child cursor counts for top SQL_IDs. Investigate any SQL_ID with more than 100 child cursors.
• Do not schedule FLUSH SHARED_POOL. It creates the problem it claims to solve.

How Netdata helps

• Per-second shared pool free memory shows fragmentation developing before the alert log fills with ORA-04031 entries. A slow decline over hours is visible long before the first error fires.
• Hard parse rate correlated with CPU and library cache mutex waits separates a parse storm from a capacity ceiling. If hard parses and mutex waits climb together, the application is the cause. If only free memory falls, look at ASMM or large allocations.
• Alert log parsing surfaces ORA-04031 as it happens, with the full message including pool name and allocation size, so you know which pool is affected without digging through ADR manually.
• V$SGA_DYNAMIC_COMPONENTS resize tracking shows ASMM thrash in real time, the early warning before ORA-04031 takes down a session.
• Correlation with RMAN job windows identifies large pool ORA-04031 cases that only appear during backup runs.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ’enq: TM - contention’: unindexed foreign keys and table-level locks
• Oracle ’enq: TX - row lock contention’: blocking sessions and uncommitted DML
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle Database monitoring checklist: the signals every production instance needs
• Oracle Database monitoring maturity model: from survival to expert
• ORA-00060: deadlock detected while waiting for resource