Fixing ORA-00240: Control File Enqueue Deadlock (Real-World Triage)

 If your alert logs are spewing ORA-00240: control file enqueue deadlock held for more than N seconds, your database is effectively frozen. Session requests are queuing up, processes are hanging on control file sequential read or control file parallel write, and RMAN backups or Data Guard broker syncs are likely ground to a halt.

I usually run into this on heavily loaded 19c RAC nodes, ODA boxes during peak batch windows, or when automated RMAN script schedules overlap with database checkpoints and snapshot operations.

Here is how I clear the deadlock, pinpoint the blocking process, and stop it from recurring.

Step 1: Kill the Blocker (Emergency Triage)

When ORA-00240 hits, you don't have time to wait for a clean timeout. First, jump in via SQL*Plus (use a pre-prepped sqlplus -prelim / as sysdba connection if standard SYSDBA logins hang):

SQL

-- Find the session holding the CF (Control File) enqueue
SELECT 
    s.sid, s.serial#, s.username, s.program, s.event, s.seconds_in_wait
FROM v$session s, v$enqueue_lock e
WHERE e.type = 'CF' 
  AND e.id1 = 0 
  AND e.id2 = 0 
  AND s.sid = e.sid;

If that query hangs or takes too long, check OS-level processes holding file locks on the control files via lsof or fuser(Linux/Unix):

Bash

# Check OS processes touching control files directly
fuser -v /u01/app/oracle/oradata/*/control01.ctl

Once you have the offending SID,SERIAL# or OS PID, kill the blocker immediately to release the enqueue lock and unfreeze the database:

SQL

-- Kill at database level
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

(If the session hangs in KILLED status, kill -9 <spid> at the OS level is your best friend).

Step 2: The Root Causes & Permanent Fixes

Clearing the session gets you back online, but unless you fix the root trigger, it’s coming back. Here are the three usual suspects:

1. RMAN Overlap with Snapshot Control Files

If RMAN attempts to back up the control file while another process (like an automated OEM job or Data Guard sync) requests a lock, you get a collision.

• Fix: Move your RMAN control file autobackup off local spinning disks or slow NFS mounts onto high-speed flash storage/ASM diskgroups.

• Tweak: Ensure RMAN isn't backing up the control file concurrently across multiple channels.

2. Slow I/O on Control File Storage

Control file writes must be fast. If disk latency spikes on the mount point holding control01.ctl or control02.ctl, enqueue waits skyrocket.

• Run an AWR/ASH report for the window and check v$event_histogram for control file parallel write. If average wait time exceeds 10ms, your storage path is choked.

3. Data Guard Broker & Observer Contention

If you run Data Guard Fast-Start Failover (FSFO), the Observer process constantly pings the control file for heartbeat checks. During intermittent network hiccups, the broker can hold onto CF enqueue locks longer than expected.

• Ensure your DG_BROKER_TIMEOUT is tuned properly so the Observer doesn't lock resources aggressively during minor network jitters.

Step 3: Quick Shell Diagnostic Script

I keep this short check in my DBA utility kit to catch control file wait spikes before they trigger full ORA-00240deadlocks:

Bash

#!/bin/bash
# Monitor Control File Enqueue Waits
sqlplus -s / as sysdba << 'EOF'
SET HEADINGS OFF PAGESIZE 0
SELECT 'WARNING: CF Enqueue Wait Count = ' || COUNT(*) 
FROM v$session_wait 
WHERE event LIKE '%control file%';
EOF

Written by Sunny Thakur. I handle Oracle Database administration, Data Guard DR setups, ODA migrations, and RAC performance tuning.

• 📌 Need Remote DBA Support? Facing urgent database outages, persistent deadlocks, or performance bottlenecks? Drop a comment or reach out directly for consulting services.

How to Fix ORA-65114: Space Usage in Container Is Too High (Oracle 19c & 23ai Multi-Tenant)

When managing Oracle Multitenant databases on on-premises ODA (Oracle Database Appliance), Exadata, or Cloud infrastructure (OCI / AWS RDS / Azure), encountering ORA-65114: space usage in container is too high can bring critical pluggable database (PDB) operations to a sudden halt.

This error occurs when a Pluggable Database (PDB) exceeds its allocated storage quota defined by the MAX_PDB_STORAGEparameter or runs out of allocated MAX_SHARED_TEMP_SIZE space.

Below is the complete, production-tested DBA step-by-step resolution playbook to diagnose and resolve ORA-65114without downtime.

1. Quick Root Cause Identification

When ORA-65114 triggers, Oracle blocks any CREATE TABLESPACE, datafile auto-extend, or temporary space allocation inside the target PDB.

First, connect to your CDB or target PDB via SQL*Plus or SQLcl and run the following diagnostic script:

SQL

-- Connect to the target PDB or check from CDB$ROOT
ALTER SESSION SET CONTAINER = your_pdb_name;

-- Check current PDB storage limits vs actual size
SELECT 
    name AS pdb_name,
    open_mode,
    total_size/1024/1024 AS current_size_mb,
    max_size/1024/1024 AS max_size_mb
FROM v$pdbs;

If current_size_mb has reached or surpassed max_size_mb, the container limit has been hit.

2. Step-by-Step Resolution Methods

Method 1: Increase the Container MAXSIZE Limit (Recommended)

The fastest fix for production environments running out of allocated container ceiling space is to adjust the PDB storage attribute:

SQL

-- Connect to CDB$ROOT or directly to the PDB with SYSDBA / PDB_ADMIN privileges
ALTER SESSION SET CONTAINER = your_pdb_name;

-- Increase storage ceiling to 100 Gigabytes
ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 100G);

-- Alternatively, set to UNLIMITED if storage is governed at the ASM/DG level
ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Method 2: Adjust Shared Temporary Space Limits

If the error occurs during heavy batch processing or sorting, the issue may stem from temporary tablespace caps:

SQL

-- Set both MAXSIZE and SHARED TEMP limits concurrently
ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 200G MAX_SHARED_TEMP_SIZE 20G);

Method 3: Identify & Reclaim Wasted PDB Storage

If hardware or cloud storage limits prevent expanding the container size, reclaim unallocated space inside the PDB:

SQL

-- Find top space-consuming segments inside the PDB
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb 
FROM dba_segments 
WHERE bytes > 100*1024*1024 
ORDER BY bytes DESC 
FETCH FIRST 10 ROWS ONLY;

• Execute ALTER TABLE <table_name> SHRINK SPACE; on tables with high high-water marks.

• Purge recycled objects: PURGE TABLESPACE <tablespace_name>;

3. Automation Script: Prevent ORA-65114 Alerts

Add this Bash / Shell monitoring snippet to your cron jobs or enterprise OEM monitoring scripts to trigger an alert before the PDB hits 90% of its MAX_PDB_STORAGE:

Bash

#!/bin/bash
# PDB Storage Threshold Monitor
ORACLE_SID=your_cdb_sid
export ORACLE_SID

sqlplus -s / as sysdba << 'EOF'
SET HEADINGS OFF FEEDBACK OFF PAGESIZE 0
SELECT name || ' is at ' || ROUND((total_size/NULLIF(max_size,0))*100,2) || '% capacity'
FROM v$pdbs 
WHERE max_size > 0 AND (total_size/max_size) > 0.90;
EOF

🛠️ Need Remote Enterprise Oracle DBA Support?

Struggling with complex Data Guard failovers, RAC performance tuning, ODA migrations, or database emergency troubleshooting?

Contact us for Remote DBA Consulting & On-Call Engineering Services across Asia-Pacific (Hong Kong, Singapore, Australia), EMEA, and North America

ORA-04031: Unable to Allocate Shared Memory - Root Cause & Resolution Guide

The ORA-04031: unable to allocate %s bytes of shared memory error occurs when Oracle fails to allocate a contiguous block of memory in the Shared Pool or Large Pool within the System Global Area (SGA). In 90% of production environments, this error is caused by Shared Pool memory fragmentation resulting from literal SQL queries without bind variables, rather than an actual lack of total physical memory. Immediate resolution involves purging specific heavy cursors or flushing the Shared Pool, while long-term prevention requires enforcing bind variables, configuring CURSOR_SHARING = FORCE, or tuning SHARED_POOL_RESERVED_SIZE.

Environment & Prerequisites

This troubleshooting guide applies across all supported Oracle Database releases, including Single Instance, Real Application Clusters (RAC), and E-Business Suite (EBS R12.2) environments. Executing the diagnostic queries and resolution commands requires administrative privileges and direct access to dynamic performance views.

• Supported Versions: Oracle Database 11g, 12c, 18c, 19c, 21c, 23c/23ai (Linux x86-64/AIX/Solaris)

• Required Roles/Privileges: SYSDBA, ALTER SYSTEM, or EXECUTE privileges on DBMS_SHARED_POOL

• Target Dynamic Views: V$SGASTAT, V$SQLAREA, V$SQL, V$SHARED_POOL_RESERVED, V$PROCESS, V$SESSION

Problem Description & Exact Error Stack

An ORA-04031 error terminates SQL statement execution, PL/SQL package compilation, or background process tasks when an allocation request for a contiguous memory chunk fails after scanning free lists and attempting to free unpinned memory.

Command Line Execution Log

SQL

SQL> EXECUTE dbms_stats.gather_table_stats('APPS', 'OE_ORDER_HEADERS_ALL');
BEGIN dbms_stats.gather_table_stats('APPS', 'OE_ORDER_HEADERS_ALL'); END;
*
ERROR at line 1:
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","executor tree")

Database Alert Log Snippet

Plaintext

2026-10-01T14:22:05.123456+08:00
Errors in file /u01/app/oracle/diag/rdbms/prod/prod1/trace/prod1_ora_54210.trc:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","SELECT /*+ ALL_ROWS */...","SQLA","tmp")

What Causes ORA-04031 in Oracle Databases?

ORA-04031 stems from contiguous memory allocation failures in the SGA, primarily driven by memory fragmentation, excessive SQL hard-parsing, or improper SGA subpool sizing. Distinguishing between memory exhaustion and memory fragmentation is critical to selecting the correct resolution path.

Cause Category	Primary Trigger	Diagnostic Indicator
Shared Pool Fragmentation	High rates of hard parsing from un-bound literal SQL queries	Thousands of rows in V$SQL with EXECUTIONS = 1
Insufficient Reserved Area	Large allocation requests (> 4,400 bytes) failing under load	REQUEST_FAILURES > 0 in V$SHARED_POOL_RESERVED
High Cursor Version Count	Parent cursors generating hundreds of child cursors	High VERSION_COUNT (> 100) in V$SQLAREA
Under-sized SGA / Shared Pool	Automatic Memory Management (ASMM) hitting SGA cap	V$SGASTAT showing zero free memory across subpools

How to Diagnose and Resolve ORA-04031 Step-by-Step

Resolving ORA-04031 requires identifying the affected subpool, pinpointing the SQL queries causing fragmentation, and applying targeted memory purging or initialization parameter adjustments. Follow these sequential steps to diagnose and clear the condition without restarting the database.

Step 1: Check Shared Pool Free Memory and Subpool Allocations

Execute the following query against V$SGASTAT to analyze memory distribution across Shared Pool components:

SQL

SELECT 
    subpool,
    name,
    ROUND(bytes/1024/1024, 2) AS size_mb
FROM v$sgastat
WHERE pool = 'shared pool'
  AND name IN ('free memory', 'SQLA', 'KGH: NO ACCESS', 'library cache', 'row cache')
ORDER BY bytes DESC;

Sample Output:

Plaintext

SUBPOOL     NAME               SIZE_MB
----------- ------------------ -------
subpool 1   SQLA                842.15
subpool 1   KGH: NO ACCESS      410.50
subpool 1   library cache       215.30
subpool 1   free memory           8.12

Note: If free memory shows available space (e.g., > 50 MB) while ORA-04031 occurs, the issue is fragmentation (lack of small contiguous free chunks) rather than total memory exhaustion.

Step 2: Identify Un-Bound Literal SQL Queries Causing Fragmentation

Run this query to locate un-bound SQL statements polluting the Library Cache with single-use cursors:

SQL

SELECT 
    SUBSTR(sql_text, 1, 60) AS sql_snippet,
    COUNT(*) AS literal_count,
    ROUND(SUM(sharable_mem)/1024/1024, 2) AS total_mem_mb
FROM v$sqlarea
WHERE executions = 1
GROUP BY SUBSTR(sql_text, 1, 60)
HAVING COUNT(*) > 50
ORDER BY COUNT(*) DESC;

Sample Output:

Plaintext

SQL_SNIPPET                                                  LITERAL_COUNT TOTAL_MEM_MB
------------------------------------------------------------ ------------- ------------
SELECT * FROM OE_ORDER_HEADERS_ALL WHERE HEADER_ID = 100231            4120       142.50
SELECT * FROM OE_ORDER_HEADERS_ALL WHERE HEADER_ID = 100232            3890       138.10

Step 3: Check Reserved Pool Failures (V$SHARED_POOL_RESERVED)

Oracle reserves a portion of the Shared Pool for large memory allocations (> 4,400 bytes). Check if requests are failing in this region:

SQL

SELECT 
    free_space,
    avg_free_size,
    used_space,
    request_failures,
    last_failure_size
FROM v$shared_pool_reserved;

Sample Output:

Plaintext

FREE_SPACE AVG_FREE_SIZE USED_SPACE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ---------------- -----------------
  12450816         14210   54210890              142              4120

Note: If REQUEST_FAILURES > 0 and LAST_FAILURE_SIZE matches your ORA-04031 error stack, SHARED_POOL_RESERVED_SIZE must be increased.

Step 4: Immediate Emergency Remediation (Non-Disruptive Memory Release)

Option A: Purge a Specific High-Memory SQL Cursor (Preferred)

Instead of flushing the entire Shared Pool, target and remove specific memory-heavy cursors using DBMS_SHARED_POOL.PURGE:

SQL

-- Find ADDRESS and HASH_VALUE for the offending SQL_ID
SELECT address, hash_value 
FROM v$sqlarea 
WHERE sql_id = '8f3a9bc11z0q2';

-- Output example: ADDRESS = 00000003ABC1234, HASH_VALUE = 1234567890

-- Purge the specific cursor from memory:
EXEC DBMS_SHARED_POOL.PURGE('00000003ABC1234, 1234567890', 'C');

Option B: Flush the Shared Pool (System-Wide Reset)

If system-wide fragmentation blocks database operations, clear all unpinned cursors from the Shared Pool:

SQL

ALTER SYSTEM FLUSH SHARED_POOL;

Warning: Flushing the Shared Pool causes a temporary CPU spike due to subsequent hard parsing across all active application sessions.

Step 5: Apply Permanent Fixes

Fix 1: Enforce Cursor Sharing for Un-bound Applications

If application code cannot be immediately modified to use bind variables, force Oracle to substitute literals with system bind variables dynamically:

SQL

ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE=BOTH;

Fix 2: Increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE

Increase the reserved area allocation to prevent large block allocation failures:

SQL

-- Set Reserved Pool to 15% of Shared Pool
ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE = 256M SCOPE=BOTH;

-- Expand total Shared Pool size
ALTER SYSTEM SET SHARED_POOL_SIZE = 2G SCOPE=BOTH;

How to Verify the ORA-04031 Fix?

Verification requires confirming that memory allocation requests succeed without raising errors and that V$SHARED_POOL_RESERVED stops accumulating failure counts.

Query V$SHARED_POOL_RESERVED to verify zero new request failures following the fix:

SQL

SELECT 
    request_failures, 
    request_misses, 
    free_space 
FROM v$shared_pool_reserved;

Sample Output:

Plaintext

REQUEST_FAILURES REQUEST_MISSES FREE_SPACE
---------------- -------------- ----------
               0              0   42108900

Verify that contiguous free memory is available in the target subpools:

SQL

SELECT 
    subpool, 
    bytes/1024/1024 AS free_mb 
FROM v$sgastat 
WHERE pool = 'shared pool' 
  AND name = 'free memory';

Sample Output:

Plaintext

SUBPOOL    FREE_MB
---------- -------
subpool 1   184.50
subpool 2   192.10

Prevention Best Practices & Sizing Formulas

Preventing future ORA-04031 occurrences relies on proactive memory sizing, enforcing bind variable standards in development, and pinning essential PL/SQL objects into memory during startup.

Reserved Pool Sizing Formula

Set SHARED_POOL_RESERVED_SIZE between 10% and 15% of the total SHARED_POOL_SIZE:

$$\text{SHARED\_POOL\_RESERVED\_SIZE}=\text{SHARED\_POOL\_SIZE}\times 0.10$$

Pin Critical Packages into Memory

Keep core application packages pinned in the Shared Pool to prevent them from aging out and causing memory fragmentation upon re-allocation:

SQL

-- Pin heavy packages on database startup
EXEC DBMS_SHARED_POOL.KEEP('APPS.OE_ORDER_GRP', 'P');
EXEC DBMS_SHARED_POOL.KEEP('DBMS_STANDARD', 'P');