ORA-04021: timeout occurred while waiting to lock object

ORA- 04021: Timeout Occurred while waiting to lock object

With following Queries, you can fetch the data which object is in wait and which session is caused wait: 

V$ACCESS – V$ACCESS view to see which users have locks on which objects in your database

SELECT SID, OWNER, OBJECT, TYPE FROM V$ACCESS WHERE OBJECT = 'object_name';

 
V$SESSION_EVENT and V$SESSION_WAIT – use these views to see what Oracle wait events the session(s) are waiting on

--It will give information about events:
SELECT * FROM V$SESSION_EVENT WHERE SID = ORDER BY TIME_WAITED DESC;

-- Check the wait session, how long it in wait
SELECT * FROM V$SESSION_WAIT WHERE SID = '';

-- Check the session is active or inactive
SELECT status FROM V$SESSION where sid= '';

 
V$LOCKED_OBJECT – This view will also help you see who is locking the object If session is inactive you can also kill the session

-- Checked locked objects
select * from v$LOCKED_OBJECT;

--If session is inactive and holding lock from long time then you have option to kill the session.
alter system kill session 'sid,serial#' immediate;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.