Checking for locking in Oracle

This is a handy little script I put together to give me details about who is causing locking within your Oracle database


It also give details on what SQL the blocking user is running.


This can be useful when trying to pin down what process is causing excessive locking within your database.


== lock_details.sql == 
set Pages 10000
set lines 150


Prompt Locked Table:
select substr(dba_objects.object_name, 1, 50) as "Locked Object", v$lock.TYPE AS "Lock Type",  v$lock.LMODE AS "Lock Mode"
from dba_objects, v$lock, v$locked_object
where v$lock.sid = v$locked_object.SESSION_ID
and v$locked_object.OBJECT_ID = dba_objects.OBJECT_ID
and v$lock.block=1
/


Prompt Lock Type Key:
Prompt JI (Materialized view)
Prompt MR (Media Recovery)
Prompt ST (Disk Space Transaction)
Prompt TM (DML or Table Lock)
Prompt TX (Transaction)
Prompt


Prompt Lock Mode Key:
Prompt 1 Null
Prompt 2 Row Share            (SS)
Prompt 3 Row Exclusive        (SX)
Prompt 4 Share                (S)
Prompt 5 Share Row Exclusive  (SSX)
Prompt 6 Exclusive            (X)
Prompt


prompt Blocking Session Details
select /*+ RULE */
substr( username, 1, 12)        "USERNAME",
substr( sid, 1, 6)              "SID",
substr( serial#, 1, 7)          "SERIAL#",
substr( command, 1, 6)          "COMMAND",
substr( osuser, 1, 12)          "OSUSER",
substr( process, 1, 8)          "PROCESS",
substr( machine, 1, 6)          "MACHINE",
substr( terminal, 1, 8)         "TERMINAL",
substr( program, 1, 25)         "PROGRAM",
substr( last_call_et, 1, 12)    "LAST CALL ET"
from   v$session
where  sid in (select sid from v$lock where block=1)
/


Prompt Blocking User is Running:


select sesion.sid,
       sql_text
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.sid in (select sid from v$lock where block=1)
 order by sesion.sid, sqltext.piece
/