Oracle ORA-06500 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06500: PL/SQL Storage Error — Causes, Fixes, and Prevention

ORA-06500 is a critical Oracle error thrown when the PL/SQL engine cannot allocate the memory it needs to continue execution. It typically points to exhaustion of PGA (Program Global Area) memory, runaway collection growth, or infinite recursion consuming the call stack. Left unaddressed, this error can destabilize sessions or, in severe cases, impact the entire database instance.

Top 3 Causes

1. PGA Memory Exhaustion

When the total PGA consumed by a session (or all sessions combined) exceeds PGA_AGGREGATE_LIMIT, Oracle raises ORA-06500. This commonly happens during large batch jobs or when many concurrent sessions each consume significant PGA.

-- Check current PGA usage per session
SELECT
    s.sid,
    s.username,
    p.pga_used_mem  / 1024 / 1024 AS pga_used_mb,
    p.pga_alloc_mem / 1024 / 1024 AS pga_alloc_mb
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.username IS NOT NULL
ORDER BY p.pga_alloc_mem DESC;

-- Review and adjust PGA parameters
SHOW PARAMETER pga_aggregate_target;
SHOW PARAMETER pga_aggregate_limit;

-- Increase PGA limit (adjust to fit available system memory)
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_limit  = 8G SCOPE=BOTH;

2. Unbounded Collection Growth

Loading millions of rows into a PL/SQL collection in a single BULK COLLECT without a LIMIT clause is one of the most frequent real-world causes of ORA-06500. Memory balloons rapidly and is never released mid-loop.

-- BAD: loads entire table into memory at once
DECLARE
    TYPE t_tab IS TABLE OF employees%ROWTYPE;
    l_data t_tab;
BEGIN
    SELECT * BULK COLLECT INTO l_data FROM employees; -- dangerous on large tables
END;
/

-- GOOD: process in chunks and release memory after each batch
DECLARE
    TYPE t_tab IS TABLE OF employees%ROWTYPE;
    l_data  t_tab;
    CURSOR  c IS SELECT * FROM employees;
    c_limit CONSTANT PLS_INTEGER := 1000;
BEGIN
    OPEN c;
    LOOP
        FETCH c BULK COLLECT INTO l_data LIMIT c_limit;
        EXIT WHEN l_data.COUNT = 0;

        -- Process each row
        FOR i IN 1 .. l_data.COUNT LOOP
            NULL; -- business logic here
        END LOOP;

        l_data.DELETE; -- free memory explicitly
        COMMIT;
    END LOOP;
    CLOSE c;
END;
/

3. Infinite or Excessively Deep Recursion

A recursive PL/SQL procedure without a proper exit condition keeps stacking frames until memory runs out, triggering ORA-06500.

-- BAD: missing or broken exit condition
CREATE OR REPLACE PROCEDURE bad_proc(p_val IN NUMBER) IS
BEGIN
    bad_proc(p_val + 1); -- never stops
END;
/

-- GOOD: clear exit condition + depth guard
CREATE OR REPLACE PROCEDURE safe_proc(
    p_val   IN NUMBER,
    p_depth IN NUMBER DEFAULT 0
) IS
    c_max CONSTANT PLS_INTEGER := 50;
BEGIN
    IF p_depth > c_max THEN
        RAISE_APPLICATION_ERROR(-20001, 'Max recursion depth reached.');
    END IF;

    IF p_val <= 0 THEN RETURN; END IF;

    -- business logic
    safe_proc(p_val - 1, p_depth + 1);
END;
/

-- Better alternative: replace recursion with CONNECT BY
SELECT LEVEL, employee_id, manager_id
FROM   employees
START  WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Quick Fix Checklist

Increase PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT if system memory allows.
Add LIMIT clause to every BULK COLLECT statement.
Call collection.DELETE after processing each batch to release memory.
Add explicit recursion depth counters and hard limits to all recursive procedures.
Free temporary LOBs with DBMS_LOB.FREETEMPORARY immediately after use.

Prevention Tips

Profile before production deployment. Use DBMS_HPROF or Oracle's SQL Trace to measure PGA consumption of new PL/SQL code against realistic data volumes before releasing to production.
Enforce coding standards. Make BULK COLLECT … LIMIT mandatory in code reviews, cap single-collection sizes, and prefer set-based SQL (CONNECT BY, analytic functions) over recursive PL/SQL wherever possible.

Related Errors

Error Code	Description
ORA-04031	Shared pool (SGA) out of memory — the SGA counterpart to ORA-06500
ORA-04036	PGA used by instance exceeds `PGA_AGGREGATE_LIMIT` — a direct trigger
ORA-06502	PL/SQL numeric or value error — often appears alongside memory issues
ORA-06533	Subscript beyond count — collection boundary error related to collection misuse

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community