Sunday, May 12, 2013

Query to find Locked Tables in Oracle

Query to find Locked Objects:
select a.object_name, b.oracle_username, b.os_user_name,c.sid, c.serial# ,c.terminal, d.sql_text
from sys.dba_objects a, v$locked_object b, v$session c, v$sqltext d      
where a.object_id = b.object_id
and c.sid = b.session_id
and c.sql_hash_value = d.hash_value


select a.object_id, b.owner, b.object_name, a.session_id, a.oracle_username, a.process
from v$locked_object a, dba_objects b

where a.object_id = b.object_id;

Query to find m/c & user name having lock:

select sid, username, osuser, process, machine, program
from v$session
where sid = sid_of_hanging_session;

Query to kill Session:


Query to find PID of session:

select pid from v$process where addr=
(select paddr from v$session where sid=sid_of_hanging_session);

Whenever there is a lock in the object, if you issue a DML command on the same, it will thrown the below error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

No comments:

Post a Comment