Scops
Docs Download

Oracle problems

ORA-04036: PGA Memory Exceeds PGA_AGGREGATE_LIMIT. Which Sessions Oracle Ends, Why, and the Fix

3 November 2026

ORA-04036 is Oracle protecting the server from itself: the private memory of all sessions together reached PGA_AGGREGATE_LIMIT, and the sessions holding the most of it were ended so that the rest could carry on. The error lands on the victim, which is rarely the cause. The sessions that grow, the kind of memory they grow in and the number to set are three queries away.

What the error says

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT (newer releases add "or PDB"). PGA is the private memory of server processes: sort and hash work areas, PL/SQL variables and collections, cursors, session state. Its total across the instance is capped by PGA_AGGREGATE_LIMIT, and when the total reaches the cap Oracle acts in two steps, treating a parallel query as one unit: first the calls of the sessions using the most untunable memory are terminated, and if the total is still over the limit, those sessions are terminated. SYS processes and background processes, except job queue processes, are left alone; they only write a summary of their PGA to a trace file.

Two neighbours mean something else:

Where the limit comes from

Most databases never set the parameter and run on its default: 200% of PGA_AGGREGATE_TARGET, or MEMORY_MAX_TARGET when MEMORY_TARGET is in use, and in all cases at least 2 GB and at least 3 MB per process in PROCESSES (5 MB on RAC). A target of 4 GB therefore gives a limit of 8 GB, and an application that keeps 20 GB of collections in memory meets it in the first busy hour. The first query shows the limit, the usage now and the high-water mark since startup:

SELECT name, ROUND(value / 1024 / 1024) AS mb
FROM   v$pgastat
WHERE  name IN ('aggregate PGA target parameter',
                'total PGA allocated',
                'total PGA inuse',
                'maximum PGA allocated')
UNION ALL
SELECT 'pga_aggregate_limit',
       ROUND(TO_NUMBER(value) / 1024 / 1024)
FROM   v$parameter
WHERE  name = 'pga_aggregate_limit';

maximum PGA allocated close to the limit says the instance has been there before. total PGA inuse far below total PGA allocated says processes keep memory they have finished with; it goes back to the operating system in time, or when the process ends.

Which sessions hold the most

The victims of ORA-04036 are the biggest sessions at that moment, so the biggest sessions now are the place to start. V$PROCESS carries the memory per server process, V$SESSION the user behind it:

SELECT s.sid, s.serial#, s.username, s.program, s.status,
       ROUND(p.pga_used_mem  / 1024 / 1024) AS used_mb,
       ROUND(p.pga_alloc_mem / 1024 / 1024) AS alloc_mb,
       ROUND(p.pga_max_mem   / 1024 / 1024) AS max_mb,
       s.sql_id
FROM   v$process p
JOIN   v$session s ON s.paddr = p.addr
WHERE  s.type = 'USER'
ORDER  BY p.pga_alloc_mem DESC
FETCH FIRST 15 ROWS ONLY;

One session with gigabytes is a program problem: a collection filled from a large table, or a statement with a huge hash join. Many sessions with the same hundreds of megabytes are a design problem: a connection pool of 500 sessions at 100 MB each is 50 GB before anyone has done anything unusual, and the limit is reached at the busiest hour, by whoever is slightly bigger. pga_max_mem shows the sessions that were big earlier and have shrunk since; that is where the evening batch hides.

What kind of memory it is

The limit acts on untunable memory first, so the category matters. V$PROCESS_MEMORY splits the PGA of a process into SQL (work areas for sorts and joins, which Oracle can shrink), PL/SQL (variables and collections, which it cannot), Freeable and Other:

SELECT pm.category,
       ROUND(pm.allocated     / 1024 / 1024) AS alloc_mb,
       ROUND(pm.used          / 1024 / 1024) AS used_mb,
       ROUND(pm.max_allocated / 1024 / 1024) AS max_mb
FROM   v$process_memory pm
JOIN   v$process p ON p.pid = pm.pid
JOIN   v$session s ON s.paddr = p.addr
WHERE  s.sid = :sid
ORDER  BY pm.allocated DESC;

PL/SQL at the top with hundreds of megabytes is a collection: a BULK COLLECT without LIMIT, a table loaded into an associative array, a loop that appends and never deletes. SQL at the top is a work area, and the statement in V$SESSION.SQL_ID is the one to look at: a hash join on an unexpected cardinality, a sort of a result that should have been filtered first. Freeable and Other are not the cause.

The fix, in order

  1. Stop the terminations now. The parameter is dynamic: ALTER SYSTEM SET pga_aggregate_limit = 16G SCOPE = BOTH;. Set it from the physical memory, not from the error: the host must hold the SGA, this limit and the operating system at the same time, or ORA-04036 turns into ORA-04030 and swapping, which ends more than one session. Never set it to 0 on a server that matters; 0 removes the limit, and the next protection is the kernel.
  2. Fix the program that grows. A collection takes rows in batches of a few thousand (BULK COLLECT ... LIMIT), is emptied when the batch is done, and is never used as a copy of a table. A work area that needs gigabytes is a plan problem, and the plan is fixed where the cardinality went wrong, not with more memory.
  3. Size the pool to the memory. Sessions times their usual PGA is the number the limit has to hold at the busiest hour. A pool that opens 1,000 connections for 50 concurrent requests pays memory for nothing; fewer, busier connections cost less and run faster.
  4. Raise the target with the limit. If over allocation count in V$PGASTAT climbs, PGA_AGGREGATE_TARGET is too small for the work areas: sorts spill to TEMP and sessions grow past the target anyway. Raise the target, and the default limit, twice the target, follows.

See it coming

The instance does not reach its limit in one second; the PGA climbs through the hour before, and the sessions that climb are visible long before the first termination. Radar Engine, the detection layer of Scops, watches the climb without an agent on the server:

DetectorClockWhen it fires
PGA near its limitLive, against pga_aggregate_limit85% of the limit, or the limit within the hour at the current climb: Warning. 95%: Critical.
Session surgeLive, against the hour beforeA pool that reconnects after its sessions were ended shows here first. 50% of the session limit, or three times the usual and rising: Warning. 75%: Critical.

The finding shows the climb and the time left to the limit, and the top sessions by memory are one click away in Scops, so the collection or the pool is found before the first ORA-04036, not from its trace file afterwards.

Questions

Why did the error land on a session that was not the big one?
ORA-04036 goes to the sessions Oracle interrupted: the ones using the most untunable PGA at that moment, or the process that was allocating shared private memory (MGA) when the limit was reached. The session that grew for an hour and finished a minute earlier is not among them; the second query finds it through pga_max_mem.

Can I set PGA_AGGREGATE_LIMIT to 0?
Yes, and it removes the limit entirely. The next protection is then the operating system, which answers with ORA-04030 and swapping, and takes every session with it. On a server that matters, set the limit from the physical memory instead.

Why does PGA_AGGREGATE_TARGET not stop this?
The target only controls tunable memory, the work areas for sorts and joins. PL/SQL collections and session state are untunable; Oracle cannot shrink them, so they grow past the target, and only the limit stops them.

Can the instance fail to start after I change it?
Yes, if the value in the spfile is below the default that Oracle computes at startup: 2 GB, 3 MB per process and twice the target, whichever is largest. Set it higher than that, never lower. If startup fails, start with a pfile that leaves it out and set it again afterwards.

Related problems

Sources