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;