πŸ“ž +880 1715-151882 βœ‰οΈ info@khannasir.com

ORA-04031 on Oracle 19c: Read the Shared Pool Before You Resize It

ORA-04031 is the error everybody thinks they already understand. The shared pool is too small, so make it bigger, and the system goes quiet for a few weeks.

Then it comes back, usually at a worse hour.

That second occurrence has a shape worth recognising. SHARED_POOL_SIZE has been raised twice, each time by a round number, and nobody has looked at what is inside the pool.

That is understandable, because Oracle's own suggested action for the error does include increasing the parameter. It is the first thing the message tells you to consider when SGA_TARGET or MEMORY_TARGET is in use.

But read the action text to the end. For the second form of the message, Oracle lists three options and puts "reduce your use of shared memory" before increasing the size.

This article is about the reading you do between the error and the ALTER SYSTEM. It is all read-only, it runs on the instance that is already up, and it changes what you type next often enough to be worth doing every time.

Key Takeaways

  • ORA-04031 reports one allocation request that failed, and the size of that request is the first clue. Oracle's cause is "More shared memory is needed than was allocated in the shared pool or Streams pool."
  • Oracle's own action text puts "reduce your use of shared memory" before "increase SHARED_POOL_SIZE".
  • Under SGA_TARGET, a manually set SHARED_POOL_SIZE is a floor, not a fixed size: Oracle says manually specified sizes "serve as a lower bound for the size of the components".
  • V$MEMORY_RESIZE_OPS keeps "the last 800 completed memory resize operations" β€” repeated SHRINK on the shared pool is a different diagnosis from a pool that was never big enough.
  • V$SHARED_POOL_ADVICE estimates parse time, not safety. It does not predict a failed allocation.
  • On Linux, Oracle says that if USE_LARGE_PAGES is ONLY and you set MEMORY_TARGET, "the instance will fail to start".
  • SGA_MIN_SIZE is a per-PDB minimum, not a shared pool floor β€” and in a PDB with no CDB-level SGA_TARGET it is ignored without an error.
  • Oracle's Real-World Performance group recommends against CURSOR_SHARING=FORCE outside rare temporary situations.

1. What the Message Is Actually Telling You

ORA-04031 is not a report on the state of your shared pool. It is a report on one allocation request that could not be satisfied at one moment.

Oracle publishes the error in two forms. The first is out of shared memory in heap_name (string), with the cause "More shared memory was needed than was available."

The second is the one most DBAs see:

ORA-04031: unable to allocate nnnn bytes of shared memory
           ("pool","name","type","allocation comment")

Its documented cause is "More shared memory is needed than was allocated in the shared pool or Streams pool."

Read the number first

The byte count at the front of the message is the size of the request that failed, and it changes the diagnosis more than anything else in the line.

Read the request size as the first hypothesis, not as the answer. A failure on a few hundred bytes points at a pool with very little usable memory left anywhere, because a chunk that small could have been satisfied in most places; confirm it against free memory in V$SGASTAT and the reserved-list counters in V$SHARED_POOL_RESERVED. A failure on several megabytes while small allocations keep succeeding points the other way: there may be plenty of free memory, but not enough of it in one contiguous piece.

That second case is fragmentation, and it is why Oracle's action text mentions SHARED_POOL_RESERVED_SIZE in the same breath as SHARED_POOL_SIZE. We come back to it in section 9.

Read the last string second

The fourth string in the message is the allocation comment, the internal label for what the memory was wanted for. It is not something you can look up in a parameter list, and it is not something I would build an action on by itself.

What it is good for is routing. A comment that points at the library cache sends you to the SQL being parsed; one that points at the Streams pool sends you to STREAMS_POOL_SIZE, which Oracle's action text names explicitly for that case.

This is the same habit I describe in my article on diagnosing any Oracle error with a systematic method: the message narrows the search, the database answers the question.

Rows of numbered metal mailbox doors in black and white, each with a hand-written number from 31 to 40
The SGA is not one undivided space. V$SGASTAT names every compartment and the bytes in it. Illustrative photograph. Photo: Nikita Nikitin / Pexels

2. Which Memory Manager Are You Running? Read It, Do Not Assume

Every command in the rest of this article depends on the answer, and on an inherited database the answer is often not what the previous DBA documented.

Oracle has two automatic modes. They are easy to tell apart from the instance itself, and that is where you should get it from.

What is setModeWhat Oracle tunes for you
MEMORY_TARGET non-zeroAutomatic memory management (AMM)Moves memory between the SGA and the instance PGA, and tunes the components inside each
MEMORY_TARGET = 0, SGA_TARGET non-zeroAutomatic shared memory management (ASMM)Holds the SGA total and resizes the components inside it
Both zeroManualNothing. Every pool is the size you set it to

Oracle describes AMM plainly: "You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands."

For ASMM it says you enable the feature "by setting the SGA_TARGET initialization parameter to a nonzero value", and that this parameter "sets the total size of the SGA".

Three lines in SQL*Plus settle it:

SHOW PARAMETER memory_target
SHOW PARAMETER sga_target
SHOW PARAMETER shared_pool_size

Do this before anything else. It is how you avoid tuning a parameter the running instance is ignoring β€” the pfile on disk and the spfile in use can drift apart, and a failover is one of the ways that happens.

3. The Linux Trap: MEMORY_TARGET and Large Pages

If your database runs on Linux and somebody has configured HugePages, there is a documented interaction here that turns a tuning change into a failed start-up.

Oracle's 19c Reference puts it in the MEMORY_TARGET page, and it is worth quoting exactly because the second sentence is unusually blunt for the documentation.

"On Linux platforms only, Oracle recommends against explicitly setting values for MEMORY_TARGET and MEMORY_MAX_TARGET in the initialization parameter file at the time of instance startup. Doing so may result in suboptimal small page allocation in the SGA. Furthermore, if the USE_LARGE_PAGES parameter is set to ONLY and you attempt to set values for MEMORY_TARGET and MEMORY_MAX_TARGET, the instance will fail to start."

Two things follow from that, and only two.

First, on Linux the practical answer to ORA-04031 is usually ASMM, not AMM, because AMM and USE_LARGE_PAGES=ONLY cannot both be in force. Second, the warning says "On Linux platforms only", so do not carry it over to AIX, Solaris or Windows without checking the Reference for the platform in front of you.

There is one more line on that page worth keeping in mind during capacity work. Oracle notes that "Total memory usage can grow beyond the value of MEMORY_TARGET", giving PL/SQL tables and varrays as the example, so the parameter is not a hard ceiling on the process's memory footprint.

4. Reading the Pool: What Is Actually In There

Now the first real measurement. V$SGASTAT "displays detailed information on the system global area", one row per component, and it is the view that turns "the pool is full" into "the pool is full of this".

Its columns are simple. POOL "designates the pool in which the memory in NAME resides: in-memory pool, java pool, large pool, numa pool, shared pool, or streams pool"; NAME is the "SGA component name"; BYTES is the "Memory size (in bytes)".

Start with the totals, so you know the shape before the detail:

SELECT pool,
       ROUND(SUM(bytes)/1024/1024, 1) AS mb
  FROM v$sgastat
 WHERE pool IS NOT NULL
 GROUP BY pool
 ORDER BY mb DESC;

Then the twenty largest pieces of the shared pool, which is where the answer usually is:

SELECT *
  FROM (SELECT name,
               ROUND(bytes/1024/1024, 2) AS mb
          FROM v$sgastat
         WHERE pool = 'shared pool'
         ORDER BY bytes DESC)
 WHERE ROWNUM <= 20;

And the free memory in each pool, which is the figure people quote at each other in incident calls:

SELECT pool,
       ROUND(bytes/1024/1024, 1) AS free_mb
  FROM v$sgastat
 WHERE name = 'free memory'
   AND pool IS NOT NULL;

How to read what comes back

The component names Oracle reports here are internal labels; the documentation defines the column as "SGA component name" and does not publish a catalogue of the values. So treat them as evidence about proportion, not as parameters you can set.

What matters is the shape of the list. If one or two names hold most of the pool, you have a specific consumer to chase, and that is a much better position than "the pool is too small".

If free memory in the shared pool is comfortable at the moment the error was raised, read it as a contiguity problem rather than a volume problem β€” and then confirm that reading against the reserved-list failures in section 9 and the request size in the message itself. Free memory is a total; it does not tell you the size of the largest single piece.

An old round Fahrenheit gauge mounted on grey machinery in a workshop, with a pendant lamp lit behind it
Read the gauge, then turn the valve. The advisory tells you what more memory would buy. Illustrative photograph. Photo: Brett Sayles / Pexels

5. The Advisory Estimates Parse Time, Not Safety

V$SHARED_POOL_ADVICE is the right view to consult at this point, as long as you are honest with yourself about the question it answers.

Oracle's description is one sentence: the view "displays information about estimated parse time in the shared pool for different pool sizes". The simulated range runs "from 10% of the current shared pool size or the amount of pinned library cache memory (whichever is higher) to 200% of the current shared pool size, in equal intervals".

SELECT shared_pool_size_for_estimate AS size_mb,
       shared_pool_size_factor       AS factor,
       estd_lc_time_saved            AS parse_secs_saved,
       estd_lc_load_time             AS estd_parse_secs
  FROM v$shared_pool_advice
 ORDER BY shared_pool_size_for_estimate;

ESTD_LC_TIME_SAVED is the "Estimated elapsed parse time saved (in seconds), owing to library cache memory objects being found in a shared pool of the specified size". ESTD_LC_LOAD_TIME is the "Estimated elapsed time (in seconds) for parsing in a shared pool of the specified size".

Read the saved-time column down the rows. Where it stops rising meaningfully as the size factor climbs, extra memory has stopped buying you parse time, and growing the pool past that point is spending RAM the buffer cache could use.

The limit of this view

Nothing in those column descriptions is about allocation failures. A pool can sit comfortably on the flat part of the advisory curve and still raise ORA-04031 on a large contiguous request.

So use the advisory to choose a size. Do not use it to declare the problem solved.

6. The Resize History: Who Is Starving Whom

This is the step most people skip, and on an ASMM or AMM instance it is frequently where the real story is.

V$MEMORY_RESIZE_OPS "displays information about the last 800 completed memory resize operations (both automatic and manual)". It excludes in-progress operations, and "All sizes are expressed in bytes".

SELECT component,
       oper_type,
       oper_mode,
       ROUND(initial_size/1024/1024) AS from_mb,
       ROUND(final_size/1024/1024)   AS to_mb,
       status,
       TO_CHAR(start_time, 'DD-MON HH24:MI') AS started
  FROM v$memory_resize_ops
 ORDER BY start_time DESC
 FETCH FIRST 40 ROWS ONLY;

OPER_TYPE carries the documented values STATIC, INITIALIZING, DISABLED, GROW, SHRINK and SHRINK_CANCEL. OPER_MODE is MANUAL, DEFERRED or IMMEDIATE, and STATUS is the completion status: INACTIVE, PENDING, COMPLETE, CANCELLED or ERROR.

The three patterns worth recognising

  • Shared pool SHRINK, buffer cache GROW, repeatedly. The instance is moving memory away from the pool because its own measurements say the cache wants it more. A floor under SHARED_POOL_SIZE is the move this pattern points to β€” check the component's own MIN_SIZE and USER_SPECIFIED_SIZE in V$MEMORY_DYNAMIC_COMPONENTS before you choose the number β€” and section 10 explains why a floor does not disable the tuning.
  • Shared pool GROW right up to the error, then the error anyway. The instance was already moving memory towards the pool. That points at the SGA total rather than the split inside it, which is a reading to check against SGA_TARGET, the component's MAX_SIZE and what the operating system actually has free β€” not a conclusion the view can reach on its own.
  • Almost no rows at all. Either the instance restarted recently, or you are in manual mode and nothing is being tuned. Both are useful to know before you write a change request.

Pair it with the current sizes, which Oracle exposes in a view it describes as showing "the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA":

SELECT component,
       ROUND(current_size/1024/1024) AS current_mb,
       ROUND(min_size/1024/1024)     AS min_mb,
       ROUND(user_specified_size/1024/1024) AS user_set_mb
  FROM v$memory_dynamic_components
 WHERE current_size > 0
 ORDER BY current_size DESC;
A close-up of an old handwritten ruled ledger in low light, with dated entries in columns
The instance keeps its own logbook: the last 800 resize operations, with times. Illustrative photograph. Photo: Mhayim Miam / Pexels

7. Why the Size Can Be the Symptom

A recurring ORA-04031 is not always a badly sized pool. One engine behind it - the one Oracle’s own Real-World Performance material describes in mechanism - is application SQL that cannot be shared.

Oracle's own Real-World Performance material is direct about the mechanism. It lists among the major problems of using literal values: "If every statement is hard parsed, then cursors are not shared, and so the database must consume more memory to create the cursors."

And the knock-on effect goes beyond memory: "Oracle Database must latch the shared pool and library cache when hard parsing. As the number of hard parses increases, so does the number of processes waiting to latch the shared pool. This situation decreases concurrency and increases contention."

So a pool stuffed with thousands of near-identical statements is not just full. It is also the reason sessions are queueing.

Finding the duplicates

Oracle exposes a signature for statements that differ only in their literals, which makes the check short. The query below groups by that signature and shows the worst offenders:

SELECT force_matching_signature,
       COUNT(*)                        AS versions,
       ROUND(SUM(sharable_mem)/1024/1024, 1) AS mb_in_pool,
       MIN(sql_text)                   AS one_example
  FROM v$sqlarea
 WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(*) > 50
 ORDER BY SUM(sharable_mem) DESC
 FETCH FIRST 15 ROWS ONLY;

A result with a few thousand versions of one signature, holding a substantial slice of the pool, is your answer. The fix is in the application: bind variables where literals are being concatenated into SQL.

That is a development change, not a DBA change, and it takes longer than an ALTER SYSTEM. Where duplication really is the engine, it is the change that removes it; a larger pool buys headroom and leaves the duplication where it was.

The threshold of 50 in that query is mine, not Oracle's. Lower it on a quiet system and raise it on a busy one; it is a filter to keep the output readable, not a rule.

8. The CURSOR_SHARING=FORCE Temptation

Somebody in the room will suggest it, because it looks like the database-side fix for an application-side problem. Oracle's documentation is unusually plain about why that is a trap.

The parameter itself "determines what kind of SQL statements can share the same cursors", and the default is EXACT, which "only allows statements with identical text to share the same cursor". FORCE "allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal".

Then the SQL Tuning Guide lists the drawbacks, and they are specific rather than vague.

  • "It indicates that the application does not use user-defined bind variables, which means that it is open to SQL injection."
  • "The database must perform extra work during the soft parse to find a similar statement in the shared pool."
  • "The database removes every literal, which means that it can remove useful information… The use of system-generated bind variables where literals are more optimal can have a negative impact on execution plans."
  • "There is an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals in a SELECT statement."
  • "Star transformation is not supported."

Oracle's conclusion is that the Real-World Performance group recommends against setting CURSOR_SHARING to FORCE, except in rare temporary situations.

My own practice follows that. I will set it at session level, or for a limited window on a system that is failing right now and cannot be patched today, and I write the removal into the same change request that introduces it.

One detail to note on 19c: the SIMILAR value many of us used years ago is no longer listed among the valid values on the parameter page. If a migrated pfile still carries it, deal with it before the upgrade rather than during the first outage after it.

9. The Reserved Area and Large Requests

Back to the fragmentation case from section 1: plenty of free memory, and still a failure on a large request.

Oracle provides a parameter for exactly this. SHARED_POOL_RESERVED_SIZE "specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory".

The documented purpose is as clear as the definition: "You can use this parameter to avoid performance degradation in the shared pool in situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request."

The numbers that matter are these. The default is "5% of the value of SHARED_POOL_SIZE", the minimum is 5000, and the maximum is "half of the value of SHARED_POOL_SIZE".

What the documentation does not say

You will find "set it to 10%" repeated across the internet, often with confidence. It is not in the 19c Reference page for the parameter, which gives the default, the range, and a pointer to the Performance Tuning Guide for sizing β€” and no recommended figure.

I mention this because a number with no source behind it is exactly the kind of thing that survives in a runbook for ten years. If you raise the reserved area, raise it because you measured large-request failures, and write down what you measured.

You can read the current state directly:

SELECT free_space, max_free_size, requests, request_misses,
       request_failures, last_failure_size
  FROM v$shared_pool_reserved;

Those are the columns the 19c Reference documents for this view: FREE_SPACE is "total amount of free space on the reserved list in bytes", MAX_FREE_SIZE "the size of the largest free piece of memory on the reserved list", and REQUEST_FAILURES the "number of times that no memory was found to satisfy a request" β€” the page ties that counter to ORA-04031 itself. The view has other columns too; check your own release's reference before you build a script around any of them.

REQUEST_FAILURES climbing with a large LAST_FAILURE_SIZE, while the ordinary pool has free memory, points at the reserved area rather than at overall pool size β€” so it is the evidence that would make SHARED_POOL_RESERVED_SIZE worth examining before SHARED_POOL_SIZE. Confirm it against your resize history and the request sizes in the error text; one counter on its own does not settle which parameter to change.

A black and white view of a loading-bay ramp with KEEP CLEAR painted on the shutter and again on the ground, and arrows marking the lane
A reserved area is space kept clear so that something large can still get through. Illustrative photograph. Photo: Sonny Sixteen / Pexels

10. Floors, Not Fixed Sizes

Here is the piece of the documentation that changes how most DBAs work, and it is one sentence long.

Oracle's Administrator's Guide says: "When automatic shared memory management is enabled, the manually specified sizes of automatically sized components serve as a lower bound for the size of the components."

It says the same thing again from the other direction when describing how to enable ASMM: you can "set one or more of the automatically sized SGA components to a nonzero value, which is then used as the minimum setting for that component during SGA tuning".

So setting SHARED_POOL_SIZE while SGA_TARGET is in force does not switch automatic tuning off for that component. It puts a floor under it.

-- A floor under the shared pool while ASMM keeps tuning above it.
-- Choose the figure from YOUR advisory and resize history, not from this page.
ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

That is the correct response to the first pattern in section 6: the instance kept shrinking the pool to feed the buffer cache, and you are telling it how far it may go.

It is the wrong response to the second pattern, where the instance was already growing the pool as far as the SGA total allowed. There, the floor changes nothing: the pool was not being squeezed by another component, it had run out of room overall.

Raising the SGA total is one way forward, and on a host with the memory to spare it is often the simplest. It is not the only one β€” Oracle's own action text for the error puts reducing shared memory use ahead of enlarging it, and depending on how the instance is configured you may also have the memory mode, the other pools and the reserved area to work with.

Decide between them from your resize history and advisory, not from this page.

A caution about SCOPE

Use SCOPE=BOTH only when you are confident, because it writes to the spfile as well as the running instance. On a system you do not know well, SCOPE=MEMORY first gives you an instance restart as a free undo.

And whatever you set, record the before and after. The same discipline applies here as in a practical method for Oracle performance tuning: a change with no recorded baseline cannot be evaluated, only defended.

11. The Other Tenants of the SGA

The shared pool does not fail in isolation. Several features draw on SGA memory, and a change to one of them can show up as ORA-04031 somewhere else.

The result cache is the one that catches people out, because its default is not a figure you can recite. Oracle says RESULT_CACHE_MAX_SIZE "specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache", and that its default value is "Derived from the values of SHARED_POOL_SIZE, SGA_TARGET, and MEMORY_TARGET".

That is all the Reference commits to. You will see specific percentages quoted elsewhere; they are not on that page, so read the running value instead of assuming one:

SHOW PARAMETER result_cache_max_size
SHOW PARAMETER streams_pool_size
SHOW PARAMETER large_pool_size
SHOW PARAMETER java_pool_size

The practical point is that raising SGA_TARGET can quietly raise the derived result cache ceiling too. If you have just grown the SGA and the pool is still tight, that is one of the places the new memory may have gone.

Oracle's action text for ORA-04031 names the other pools by their own parameters. "If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE", and for an Oracle Streams or XStream process, increase STREAMS_POOL_SIZE or the capture or apply parameter MAX_SGA_SIZE.

Which is why the first column of the first query in section 4 is POOL. Fixing the shared pool when the message came from the large pool is a day spent on the wrong room.

12. Pinning: What DBMS_SHARED_POOL Will and Will Not Do

Oracle's action text for ORA-04031 opens with pinning, and on systems with a few very large PL/SQL packages it genuinely helps.

The package "provides access to the shared pool, which is the shared memory area where cursors and PL/SQL objects are stored", and it "enables you to display the sizes of objects in the shared pool, and mark them for keeping or not-keeping in order to reduce memory fragmentation".

The reasoning Oracle gives for KEEP is the fragmentation argument stated plainly: "When large objects are brought into the shared pool, several objects may need to be aged out to create a contiguous area large enough." A kept object "is not subject to aging out of the pool".

Find the candidates before you pin anything. SIZES "shows objects in the shared_pool that are larger than the specified size", and it gives you the name to pass to KEEP:

SET SERVEROUTPUT ON SIZE 1000000
EXEC DBMS_SHARED_POOL.SIZES(minsize => 2000);   -- KB

-- then, for a large package you have confirmed is used constantly:
EXEC DBMS_SHARED_POOL.KEEP('APP_OWNER.BILLING_PKG', 'P');

The flag is the object type. Oracle documents 'P' for a package, procedure or function, 'Q' for a sequence, 'R' for a trigger, 'T' for a type, 'C' for a cursor, and the JS, JC and JD values for Java source, class and shared data.

Two honest limits

Pinning is for a handful of objects you have measured, not a list of forty. Memory you pin is memory the instance can no longer reclaim under pressure, so an over-enthusiastic pin list makes the next ORA-04031 arrive sooner.

And note Oracle's own warning on UNKEEP: "This procedure may not be supported in the future if automatic mechanisms are implemented to make this unnecessary." A documented future-support caveat is a good reason to keep your dependency on the package small.

13. What SGA_MIN_SIZE Does Not Do

The name promises a guaranteed minimum SGA, and in a lot of runbooks it is written up as the floor to set when the shared pool keeps shrinking. That is not what the parameter is.

Oracle's Reference is one line: "SGA_MIN_SIZE indicates a possible minimum value for SGA usage of a pluggable database (PDB)."

It is a multitenant parameter, and its constraints reflect that. The range is "0 to 50% of SGA_TARGET", and the value must be no more than half of SGA_TARGET both in the PDB and at the CDB level.

The sentence to remember is the failure mode: "If SGA_TARGET is not set at the CDB level, then setting SGA_MIN_SIZE in a PDB has no effect. You will not receive an error message, and the PDB's SGA_MIN_SIZE value will not be enforced."

A parameter that accepts your value, reports success and then does nothing is the worst kind to have in a runbook, because every subsequent incident is reasoned about as though the floor were in place. If you work on container databases, this one and the per-PDB memory controls in my guide to Oracle multitenant CDB and PDB administration are worth reading together.

For the ordinary single-instance case in this article, the floor you want is the one in section 10: SHARED_POOL_SIZE under SGA_TARGET.

14. The Read-Only Runbook

This is the order I work in, and the reason each step comes where it does. Steps 1 to 6 are read-only.

#What you doWhat it rules in or out
1Read the failed request size and the last string in the alert log entryVolume vs contiguity; shared pool vs large or Streams pool
2SHOW PARAMETER for memory_target, sga_target, shared_pool_sizeWhich mode you are in, and therefore which knob exists
3V$SGASTAT by pool, then the top 20 shared pool namesWhether one consumer dominates
4V$MEMORY_RESIZE_OPS, last 40 rowsWhether the instance is shrinking the pool, or already maxed out
5V$SHARED_POOL_RESERVED failures and last failure sizeWhether large contiguous requests are the problem
6V$SQLAREA grouped by FORCE_MATCHING_SIGNATUREWhether non-shared SQL is the engine behind it
7V$SHARED_POOL_ADVICE to pick a sizeWhere extra memory stops buying parse time
8Set the floor, or raise the SGA total, and record the baselineThe change, now with a reason attached to it

What to put in the ticket

Write down the failed request size, the mode, the dominant component, and whichever of the three resize patterns you matched. Those four facts are what the next occurrence starts from, instead of starting from nothing.

If step 6 found thousands of versions of one signature, the ticket does not close with the ALTER SYSTEM. It closes when the application binds its variables, and the memory change is recorded as what it was: the thing that kept the system up until then.

The honest summary

Increasing the shared pool is a legitimate action, documented by Oracle, and on the night of an incident it is often the right one.

What separates that from guesswork is the reading you do before it. The instance will tell you its mode, its component sizes, its last 800 resize decisions, its reserved-area failures and the SQL filling its library cache, and it will tell you in read-only queries that cost almost nothing.

Read those first, and the number you then set is a decision rather than a hope.

Frequently Asked Questions

What does ORA-04031 actually mean?

Oracle gives it in two forms. One is "out of shared memory in heap_name", whose cause is "More shared memory was needed than was available." The other is "unable to allocate string bytes of shared memory" followed by four strings, whose cause is "More shared memory is needed than was allocated in the shared pool or Streams pool."

In both cases a single allocation request failed. The number at the front of the second form is the size of the request that failed, which is the most useful figure in the whole message, because a failure on a few hundred bytes and a failure on several megabytes point at different problems.

Should I just increase SHARED_POOL_SIZE?

It is one of Oracle's documented actions, and on a production system that is throwing the error it is often the fastest relief. It is not a diagnosis, and it is not the only option on the list: which one fits depends on what the message says failed, whether it is the reserved pool that is short, and whether the memory mode in use leaves the instance any memory to give.

Oracle's action text for ORA-04031 lists three options in order: use DBMS_SHARED_POOL to pin large packages, reduce your use of shared memory, or increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. Note that reducing use is listed before increasing size. If non-shared SQL is filling the pool, a bigger pool fills more slowly and still fills.

Am I running automatic memory management or automatic shared memory management?

Read it from the instance rather than from the documentation set you happen to remember. A non-zero MEMORY_TARGET means automatic memory management, where Oracle redistributes memory between the SGA and the instance PGA. A zero MEMORY_TARGET with a non-zero SGA_TARGET means automatic shared memory management, where the SGA total is fixed and the components inside it are tuned.

Both answers change what your next command should be, so check SHOW PARAMETER before you change anything.

Is it safe to set MEMORY_TARGET on Linux?

Oracle's 19c Reference carries a specific warning for that platform. It says that on Linux platforms only, Oracle recommends against explicitly setting values for MEMORY_TARGET and MEMORY_MAX_TARGET in the initialization parameter file at instance startup, because it may result in suboptimal small page allocation in the SGA.

It adds that if USE_LARGE_PAGES is set to ONLY and you attempt to set those values, the instance will fail to start. That note is written for Linux and should not be assumed to apply to AIX, Solaris or Windows.

Does setting SHARED_POOL_SIZE under SGA_TARGET disable automatic tuning?

No, and this is the most useful thing to know about the parameter. Oracle's Administrator's Guide says that when automatic shared memory management is enabled, the manually specified sizes of automatically sized components serve as a lower bound for the size of the components.

You can set one or more of them to a non-zero value, which is then used as the minimum setting for that component during SGA tuning. So a SHARED_POOL_SIZE under SGA_TARGET is a floor, not a fixed size: the instance may still grow the pool above it, but it will not shrink it below.

What is SGA_MIN_SIZE for?

It is not a shared pool floor, which is what the name suggests to most people. Oracle's Reference says SGA_MIN_SIZE indicates a possible minimum value for SGA usage of a pluggable database. Its range is 0 to 50% of SGA_TARGET, and it must be no more than half of SGA_TARGET both in the PDB and at the CDB level.

If SGA_TARGET is not set at the CDB level, setting SGA_MIN_SIZE in a PDB has no effect, and the documentation adds that you will not receive an error message, so it fails silently.

Will CURSOR_SHARING=FORCE fix my shared pool?

Oracle's Real-World Performance group recommends against setting CURSOR_SHARING to FORCE except in rare temporary situations. The documented drawbacks are specific: it indicates the application does not use user-defined bind variables and is therefore open to SQL injection; the database does extra work during the soft parse; removing every literal can remove useful information and have a negative impact on execution plans; the maximum lengths returned by DESCRIBE increase for selected expressions containing literals; and star transformation is not supported.

Treat it as a temporary measure while the application is fixed, not as a setting you leave in place.

Does V$SHARED_POOL_ADVICE tell me whether ORA-04031 will come back?

No, and it is worth being precise about this. The view "displays information about estimated parse time in the shared pool for different pool sizes". ESTD_LC_TIME_SAVED is estimated elapsed parse time saved and ESTD_LC_LOAD_TIME is estimated elapsed parse time, both in seconds.

It simulates sizes from 10% of the current shared pool size, or the amount of pinned library cache memory if that is higher, up to 200% of the current size. That tells you where extra memory stops buying parse time. It does not predict a failed allocation.

How do I find out whether the instance is shrinking my shared pool?

Query V$MEMORY_RESIZE_OPS, which displays information about the last 800 completed memory resize operations, both automatic and manual, with all sizes expressed in bytes. Its companion V$MEMORY_DYNAMIC_COMPONENTS summarises every completed resize since startup, one row per component, with CURRENT_SIZE, MIN_SIZE, MAX_SIZE, USER_SPECIFIED_SIZE and LAST_OPER_TYPE. The OPER_TYPE column carries the values STATIC, INITIALIZING, DISABLED, GROW, SHRINK and SHRINK_CANCEL, and COMPONENT names the component.

A pattern of SHRINK on the shared pool alongside GROW on the buffer cache, repeating over days, says the instance is moving memory away from the pool. That is a different problem from a pool that was simply never big enough, and it has a different fix.

Does pinning objects with DBMS_SHARED_POOL help?

For a small number of large, frequently used objects, yes. Oracle says the package lets you display the sizes of objects in the shared pool and mark them for keeping or not-keeping in order to reduce memory fragmentation, and that a kept object is not subject to ageing out of the pool.

The reasoning given is that when large objects are brought into the pool, several objects may need to be aged out to create a contiguous area large enough. Use the SIZES procedure to find candidates first. Note that Oracle's own documentation warns UNKEEP may not be supported in future if automatic mechanisms make it unnecessary.

🧠 Is ORA-04031 back for the second time?

If the shared pool has already been raised once and the error has returned, a second raise is worth holding until you have looked at the other three together: the resize history, the library cache and the application's SQL. I read all three, tell you which of the patterns you have, and leave your team with the read-only runbook.

πŸ“© Book an Oracle Memory Review
Nasir Uddin Khan β€” Oracle DBA Consultant

Nasir Uddin Khan

Nasir is an Oracle Certified Professional and CSV-certified IT consultant based in Dhaka, Bangladesh. He has 18+ years of hands-on experience in Oracle database administration (RAC, Data Guard, RMAN), WebLogic middleware, ERP system design, and AI integration for manufacturing, pharmaceutical, banking, and healthcare organisations worldwide.

References & Further Reading

All Oracle pages above were read on 15 and 16 November 2026 and apply to Oracle Database 19c. Parameter behaviour, defaults and view columns differ between releases and editions: check the documentation for your exact version before making a production change. The queries in this article are read-only; the ALTER SYSTEM and DBMS_SHARED_POOL examples are illustrations, and the sizes in them are placeholders, not recommendations for your system.

Related Articles

A Shared Pool That Is Sized Because Somebody Measured It

Oracle memory review Β· ORA-04031 root cause Β· library cache and cursor sharing Β· health checks. 18+ years of Oracle experience. Bangladesh and worldwide.

πŸ’¬