🧩 How to Fix ORA-04021: Timeout Occurred While Waiting to Lock Object

 🧩 Introduction

The error ORA-04021: timeout occurred while waiting to lock object is a common performance-related issue in Oracle databases. It happens when a session tries to acquire a lock on a database object (such as a package, procedure, or table), but another session is already holding that lock. If the waiting session cannot obtain the lock within the specified time, Oracle throws this error.

This can be frustrating in production environments because it often blocks critical operations such as compiling objects, running procedures, or modifying tables.

Why This Error Happens

Typical causes include:

Another user is compiling or executing the same PL/SQL object.

A long-running transaction is holding a lock on the object.

A session that crashed left behind uncommitted changes.

Multiple developers or jobs are trying to compile or recompile packages at the same time.

Lack of proper resource management in highly concurrent systems.

Example of the Error

ALTER PACKAGE hr.emp_pkg COMPILE;

If another session is using or compiling the same package, you might see:

ORA-04021: timeout occurred while waiting to lock object HR.EMP_PKG

Step-by-Step Solutions

1. Identify blocking sessions

Run the following query to find who is holding the lock:

SELECT s.sid, s.serial#, s.username, l.type, l.id1, l.id2, l.lmode, l.request

FROM v$session s, v$lock l

WHERE s.sid = l.sid

AND l.block = 1;

This shows the blocking session details.

2. Kill the blocking session (if necessary)

If you confirm the blocking session is not critical, terminate it:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

⚠️ Use this with caution in production environments.

3. Increase timeout

If the lock is temporary and you can wait longer, increase the lock wait timeout:

ALTER SESSION SET ddl_lock_timeout = 600;

This allows Oracle to wait 10 minutes before throwing the error.

4. Avoid recompiling during peak load

If the error is related to PL/SQL objects, avoid recompiling packages, procedures, or triggers during times of heavy usage. Schedule such tasks during maintenance windows.

5. Use Edition-Based Redefinition (EBR)

In modern Oracle versions, EBR allows you to create a new edition of PL/SQL objects without blocking running sessions. This reduces locking conflicts when updating application code.

6. Check for uncommitted transactions

Sometimes, locks are caused by uncommitted transactions. You can find them with:

SELECT s.sid, s.serial#, s.username, t.start_time

FROM v$transaction t, v$session s

WHERE t.ses_addr = s.saddr;

Best Practices

Monitor blocking sessions regularly with AWR or OEM.

Train developers to commit or rollback transactions quickly to release locks.

Use resource profiles to control long-running sessions.

For code changes, consider rolling deployments instead of recompiling objects in production.

Conclusion

The error ORA-04021: timeout occurred while waiting to lock object is a performance-related issue caused by object locking conflicts. By monitoring sessions, managing recompilation times, and using modern Oracle features like EBR, DBAs can minimize disruptions and keep applications running smoothly.