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