DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Tuesday, December 22, 2015

Find the object which are locked / ORA-04021: timeout occurred while waiting to lock object

Find the object which are locked

This query is for finding the object_name which is been locked
select oracle_username, os_user_name,SESSION_ID,locked_mode,object_name,object_type from v$locked_object a, dba_objects b where a.object_id=b.object_id;

This query is for finding the SID of the locked object_name
SELECT * FROM v$access WHERE OBJECT = '<object_name>';

This query is for finding the SID and Serial# of the locked SID
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id where s.sid=<Locked SID>;

This query is for killing the sid
ALTER SYSTEM KILL SESSION 's.sid, s.serial#';

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID;

1 comment:

Sridevi K said...

Sridevi Koduru (Senior Oracle Apps Trainer
LinkedIn profile -
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at | +91 - 9581017828.