Oracle ORA-04021 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04021: Timeout Occurred While Waiting to Lock Object

ORA-04021 is an Oracle error that occurs when a session attempts to acquire an exclusive lock on a database object (such as a package, procedure, trigger, or table) for a DDL operation, but the lock cannot be obtained within the allowed timeout period. This typically happens when another active session is currently using or executing the target object, preventing the DDL from proceeding. It is one of the most common errors DBAs encounter when deploying code changes to a live production environment.

Top 3 Causes

1. Active Session Executing the Target Object

When a session tries to recompile or alter a PL/SQL object (e.g., a package or procedure) while another session is actively executing it, Oracle cannot acquire the necessary exclusive lock.

-- Find sessions currently accessing a specific object
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       a.object,
       a.type
FROM   v$session s,
       v$access a
WHERE  s.sid = a.sid
AND    a.object = UPPER('YOUR_OBJECT_NAME');

-- Kill the blocking session if safe to do so
ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

2. Uncommitted Transactions Holding Table Locks

An open, uncommitted DML transaction on a table will block any DDL operation (such as ALTER TABLE or DROP TABLE) against that same table.

-- Identify sessions with open transactions
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       t.used_ublk    AS undo_blocks,
       s.last_call_et AS idle_seconds
FROM   v$session s,
       v$transaction t
WHERE  s.taddr = t.addr
ORDER  BY idle_seconds DESC;

-- Check what SQL the blocking session last ran
SELECT sql_text
FROM   v$sqlarea
WHERE  sql_id = (
    SELECT prev_sql_id
    FROM   v$session
    WHERE  sid = 123
);

3. DDL_LOCK_TIMEOUT Set to Zero (Default)

By default, DDL_LOCK_TIMEOUT is set to 0, meaning Oracle will not wait at all before throwing ORA-04021. Even a brief moment of object usage can cause the DDL to fail immediately.

-- Check current setting
SHOW PARAMETER DDL_LOCK_TIMEOUT;

-- Set at session level (seconds) before running DDL
ALTER SESSION SET DDL_LOCK_TIMEOUT = 30;

-- Or set system-wide
ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30 SCOPE = BOTH;

-- Then attempt the DDL operation
ALTER PACKAGE your_package_name COMPILE;

Quick Fix Solutions

-- Step 1: Identify all blocking locks
SELECT blocking_session,
       sid,
       serial#,
       wait_class,
       seconds_in_wait,
       event
FROM   v$session
WHERE  blocking_session IS NOT NULL
ORDER  BY seconds_in_wait DESC;

-- Step 2: Increase DDL wait time at session level
ALTER SESSION SET DDL_LOCK_TIMEOUT = 60;

-- Step 3: Retry your DDL
ALTER PROCEDURE your_procedure_name COMPILE;

Prevention Tips

1. Always perform DDL during a maintenance window.
Validate that no active sessions are using the target object before executing any DDL in production. Standardize a pre-DDL checklist using v$access and v$lock views as part of your deployment runbook.

2. Set DDL_LOCK_TIMEOUT to a reasonable value system-wide.
Configure DDL_LOCK_TIMEOUT to at least 30–60 seconds to allow transient object usage to clear before the DDL fails. Additionally, enforce application connection pool hygiene to prevent idle, uncommitted transactions from lingering and blocking DDL operations.

Related Errors

ORA-00054: Resource busy with NOWAIT — similar lock acquisition failure during DML or DDL.
ORA-04020: Deadlock detected while trying to lock object — mutual lock wait between sessions on schema objects.
ORA-00060: General deadlock detected — DML-level deadlock, often investigated alongside ORA-04021 in lock contention scenarios.

📖 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