Check the locking on the objects, SID, Module and user in Oracle

Locks in Oracle

Locking is used by oracle internal for handling the data concurrency and consistency. So, during any update , delete , insert or select query it will acquired locks on the object at table level or row level.

There are three types of  isolation level supported by oracle:
SERIALIZABLE
READ COMMITTED
READ ONLY

You can change you session level isolation level with following commands:

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

Check the SID of locking
SELECT s.sid, l.lmode, l.block, substr(s.username, 1, 10), substr(s.schemaname, 1, 10),substr(s.osuser, 1, 10), substr(s.program, 1, 30), s.command FROM   v$session s, v$lock l WHERE  s.sid=l.sid;

Check the locking on the objects
select oracle_username,os_user_name,locked_mode,object_name,object_type
from v$locked_object lo,dba_objects do
where lo.object_id = do.object_id;

Identified the locked objects
select lpad(' ',decode(l.xidusn,0,3,0)) || l.oracle_username "User",
o.owner, o.object_name, o.object_type
from v$locked_object l, dba_objects o
where l.object_id = o.object_id
order by o.object_id, 1 desc;

Detail of locking which module and user locked:
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;

For killing the blocking session refer to another post:Kill the session

 

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 )

Connecting to %s

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