Oracle problems

ORA-04031: Unable to Allocate Shared Memory. What Drains the Shared Pool, and How to See It Coming

2 November 2026

ORA-04031 looks like a sizing problem and is usually a parsing problem. The shared pool is the memory where Oracle keeps parsed SQL, PL/SQL and dictionary data. When it cannot find a free piece of the size a statement needs, the statement fails, and often it is not the statement that caused it.

What the error says

ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","SELECT ...","SQLA","..."). The parts tell you where it hurt: the pool, the statement that asked, and the area inside the pool. The pool is usually the shared pool; on a database that uses parallel query or RMAN it can be the large pool, and with GoldenGate or Streams the streams pool.

Two different failures give the same error:

• The pool drains. Free memory falls over hours until nothing is left. Something keeps adding: a flood of statements that are never reused, a growing PL/SQL cache, or automatic memory management that moved shared pool memory to the buffer cache.
• The pool is fragmented. Free memory is there, in thousands of small pieces, and the request needs one large piece. The usual cause is SQL with literal values instead of bind variables: every execution is a new statement, parsed once and never reused.

Which one you have

SELECT inst_id, pool, name, ROUND(bytes / POWER(1024, 2)) AS mb
FROM   gv$sgastat
WHERE  name = 'free memory'
AND    pool IS NOT NULL
ORDER  BY inst_id, pool;

SELECT component,
       ROUND(current_size / POWER(1024, 2)) AS mb,
       ROUND(min_size     / POWER(1024, 2)) AS min_mb,
       ROUND(max_size     / POWER(1024, 2)) AS max_mb,
       last_oper_type, last_oper_time
FROM   v$sga_dynamic_components
WHERE  current_size > 0
ORDER  BY current_size DESC;

A shared pool with 1% free memory is draining. A shared pool with 15% free and ORA-04031 is fragmented. A shared pool whose MIN_SIZE is far below its current size, with SHRINK as the last operation, was handed to the buffer cache by automatic memory management and will be again.

Then the parse rate, which is the cause in most fragmented pools:

SELECT metric_name, ROUND(value, 1) AS per_second
FROM   v$sysmetric
WHERE  group_id = 2
AND    metric_name IN ('Hard Parse Count Per Sec',
                       'Total Parse Count Per Sec',
                       'Parse Failure Count Per Sec');

Tens of hard parses a second on a busy system is normal. Hundreds is an application that builds its SQL with literals, and the next query names it:

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

FORCE_MATCHING_SIGNATURE is the same for statements that differ only in their literals. Three hundred statements with one signature are one statement written three hundred ways, and each copy took its own parse and its own piece of the pool. The Insights page of Scops keeps this list as Repeated Literals, grouped into families by that signature, for every database.

The other large consumer is a statement with hundreds of child cursors:

SELECT sql_id, version_count, executions,
       ROUND(sharable_mem / POWER(1024, 2)) AS mb
FROM   v$sqlarea
ORDER  BY version_count DESC
FETCH FIRST 10 ROWS ONLY;

V$SQL_SHARED_CURSOR says why a statement could not share its cursor: bind lengths that differ, optimizer settings that differ by session, or a language or a role that differs.

Fix it, in this order

1. Get the errors to stop. ALTER SYSTEM FLUSH SHARED_POOL; frees everything that is not pinned or in use. It costs a burst of parsing for a few minutes and buys the time for the real fix. On a RAC, run it on the instance that fails.
2. Stop the pool from being taken. With SGA_TARGET set, give the shared pool a floor it cannot shrink below: ALTER SYSTEM SET shared_pool_size = 4G SCOPE=BOTH;. Under automatic memory management, the parameter is a minimum, not a fixed size.
3. Fix the parsing. Bind variables in the application are the fix. Until the application changes, ALTER SYSTEM SET cursor_sharing = FORCE; replaces literals with binds at parse time. It changes the plans of statements that used the literal to choose, such as LIKE 'ABC%' or a range on a skewed column, so test it on the busy statements first.
4. Size it from evidence. V$SHARED_POOL_ADVICE shows what each size would have saved in parse time. V$LIBRARYCACHE and its RELOADS show cursors thrown out and parsed again.
5. Pin what must always be there. Large packages that are called all day can be kept with DBMS_SHARED_POOL.KEEP, so they do not fragment the pool when they are reloaded.

A restart fixes ORA-04031 for a day. If the cause is the parsing, the pool fills again by the next afternoon.

See it coming

A shared pool does not run out in a second; it drains over an hour or fragments over a day. Radar Engine, the detection layer of Scops, watches the free memory and the parse rate together:

The pairing is the point: free memory that falls while hard parses rise is a parsing problem, and free memory that falls while the buffer cache grows is a memory management problem. Radar reports which one it saw.

Questions

Does ORA-04031 mean I must restart the database?
No. Flush the shared pool to stop the errors, give the pool a minimum size online, and fix the parsing. A restart only postpones the next occurrence.

Why does the shared pool shrink by itself?
With SGA_TARGET set, Oracle moves memory between the shared pool and the buffer cache as it sees fit. Set shared_pool_size to the size you need; under automatic memory management it is the floor.

Is cursor_sharing = FORCE safe?
It is a stopgap, not a fix. It removes the parse storm from literal SQL but can change plans where the literal mattered. Test it, and still move the application to bind variables.

How large should the shared pool be?
There is no universal number. V$SHARED_POOL_ADVICE shows the gain of each size on your workload, and a hard parse rate in the tens per second, not hundreds, keeps any reasonable size healthy.

Related problems

• CPU at 100%
• ORA-04036: PGA memory exceeds PGA_AGGREGATE_LIMIT
• ORA-04030: out of process memory
• ORA-00020: maximum number of processes
• SQL plan changed
• library cache lock and mutex waits
• All Oracle problems

Sources

• Oracle: ORA-04031
• Oracle: V$SGASTAT, V$SGA_DYNAMIC_COMPONENTS, V$SQL_SHARED_CURSOR, CURSOR_SHARING
• Scops docs: Detector reference