DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01037 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01037: Maximum Cursor Memory Exceeded — Causes, Fixes & Prevention

ORA-01037 is thrown by Oracle Database when a cursor attempts to use more memory than the system allows for cursor operations. This typically happens during parsing or execution of SQL statements that demand excessive memory, or when too many cursors are left open simultaneously. Left unresolved, this error can cause application failures and service outages in production environments.

Top 3 Causes

1. Cursor Leaks — Open Cursors Never Closed

The most common cause is application code that opens cursors but never explicitly closes them, leading to memory accumulation per session. This is especially common in JDBC applications where PreparedStatement or ResultSet objects are not closed in finally blocks.

-- Check open cursor count per session
SELECT s.sid,
       s.username,
       s.program,
       COUNT(c.cursor_type) AS open_cursors
FROM   v$session s
JOIN   v$open_cursor c ON s.sid = c.sid
WHERE  s.username IS NOT NULL
GROUP BY s.sid, s.username, s.program
ORDER BY open_cursors DESC;

-- View current OPEN_CURSORS limit
SHOW PARAMETER open_cursors;

-- Temporarily increase limit while fixing root cause
ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

2. Insufficient PGA Memory

PGA (Program Global Area) is private memory allocated per server process. When PGA_AGGREGATE_TARGET or PGA_AGGREGATE_LIMIT is set too low, complex queries — especially those involving sorts, hash joins, or large bind variable sets — exhaust the available cursor memory.

-- Check current PGA parameters
SHOW PARAMETER pga;

-- Review actual PGA usage statistics
SELECT name, value
FROM   v$pgastat
WHERE  name IN (
    'total PGA inuse',
    'total PGA allocated',
    'maximum PGA allocated',
    'aggregate PGA target parameter'
);

-- Increase PGA target (adjust based on available system memory)
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

-- For Oracle 12c+: also set the hard limit
ALTER SYSTEM SET pga_aggregate_limit = 4G SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

3. Overly Complex SQL Statements

Queries with dozens of nested subqueries, multiple UNIONs, or deeply layered window functions force the optimizer to allocate a disproportionate amount of cursor memory during parsing and plan generation. The more complex the SQL, the more memory Oracle needs to represent the cursor internally.

-- Before: deeply nested subqueries consuming excessive cursor memory
SELECT employee_id,
       (SELECT SUM(salary) FROM salaries
        WHERE dept_id IN (
            SELECT dept_id FROM departments
            WHERE region_id = (
                SELECT region_id FROM regions
                WHERE region_name = 'ASIA'
            )
        )
       ) AS total_salary
FROM employees;

-- After: refactored with CTEs to reduce cursor memory pressure
WITH asia_region AS (
    SELECT region_id FROM regions
    WHERE  region_name = 'ASIA'
),
asia_depts AS (
    SELECT dept_id FROM departments
    JOIN   asia_region USING (region_id)
),
dept_total AS (
    SELECT SUM(salary) AS total_salary
    FROM   salaries
    JOIN   asia_depts USING (dept_id)
)
SELECT e.employee_id, d.total_salary
FROM   employees e
CROSS JOIN dept_total d;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Step 1: Identify sessions with abnormally high cursor counts
SELECT sid, serial#, username, COUNT(*) AS cur_count
FROM   v$open_cursor
GROUP BY sid, serial#, username
HAVING COUNT(*) > 300
ORDER BY cur_count DESC;

-- Step 2: Kill offending session if necessary (use with caution)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Step 3: Validate SESSION_CACHED_CURSORS setting
SHOW PARAMETER session_cached_cursors;

-- Reduce cached cursors if memory is tight
ALTER SYSTEM SET session_cached_cursors = 20 SCOPE=SPFILE;

-- Step 4: Check for SQL with high cursor open counts
SELECT sql_id, sql_text, users_opening, users_executing
FROM   v$sql
WHERE  users_opening > 5
ORDER BY users_opening DESC;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Always close cursors in application code. Enforce a coding standard that every opened cursor, PreparedStatement, or ResultSet is closed in a finally block or via try-with-resources (Java). This single practice eliminates the majority of ORA-01037 incidents.

Tune memory parameters before go-live. Run load tests before production deployments to measure peak PGA and cursor usage. Set PGA_AGGREGATE_TARGET with at least 30% headroom above observed peak usage, and monitor regularly using AWR reports and v$pgastat.

-- Useful AWR-based PGA monitoring query
SELECT snap_id,
       pga_target_for_estimate,
       pga_target_factor,
       estd_pga_cache_hit_percentage,
       estd_overalloc_count
FROM   v$pga_target_advice
ORDER BY pga_target_factor;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01000 — Maximum open cursors exceeded; closely related and often appears alongside ORA-01037.
  • ORA-04031 — Unable to allocate shared memory; indicates Shared Pool exhaustion, a companion memory issue.
  • ORA-00604 — Error at recursive SQL level; can surface together with ORA-01037 during internal Oracle operations.

📖 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.

Top comments (0)