How to check database lock conflict in Oracle

Database lock con icts are one of the issues which DBA needs to deal with. The database locks can keep users waiting for very long and we much know how to check database locks. Users reporting that their query is taking too long to execute, then you must also check if there are any locks on the objects being accessed (unless its a select query). Use below queries to check the database locks:

READ ALSO
AWS Lambda triggered by S3

Checking Lock Conflicts in 10g and Above:

sql">select a.SID "Blocking Session", b.SID "Blocked Session"
from v$lock a, v$lock b
where a.SID != b.SID and a.ID1 = b.ID1 and a.ID2 = b.ID2 and
b.request > 0 and a.block = 1;

Checking Lock Conflicts in 9i Systems:

sql">select s1.username || '@' || s1.machine
' ( SID=' || s1.sid || ' ) is blocking '
username || '@' || s2.machine
' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;


Query to Check Lock is Table Level or Row Level

sql">col session_id head 'Sid' form 9999
col object_name head "Table|Locked" form a30
col oracle_username head "Oracle|Username" form a10 truncate col os_user_name head "OS|Username" form a10 truncate col process head "Client|Process|ID" form 99999999
col owner head "Table|Owner" form a10
col mode_held form a15
select lo.session_id,lo.oracle_username,lo.os_user_name,
lo.process,do.object_name,do.owner,
decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)',
3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive',
to_char(lo.locked_mode)) mode_held
from gv$locked_object lo, dba_objects do
where lo.object_id = do.object_id
order by 5
/






READ ALSO
How to check temp tablespace utilization

Leave a Reply

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