To find information about locks in the database

To check locks in the database:

col schema format a8

col object format a20

select substr(a.os_user_name,1,9)    "OS User" 

,      substr(a.oracle_username,1,8) "DB User" 

,      substr(b.owner,1,8)           "Schema" 

,      e.sid      "Sid"

,      substr(b.object_name,1,20)    "Object" 

,      substr(b.object_type,1,10)    "Type" 

,      substr(c.segment_name,1,5)    "RBS" 

,      substr(d.used_urec,1,12)      "# of Records" 

from v$locked_object a 

,    dba_objects b 

,    dba_rollback_segs c 

,    v$transaction d 

,    v$session e 

where a.object_id =  b.object_id 

  and a.xidusn    =  c.segment_id 

  and a.xidusn    =  d.xidusn 

  and a.xidslot   =  d.xidslot 

  and d.addr      =  e.taddr;

Locksid

====

select substr(a.os_user_name,1,9)    "OS User"

,      substr(a.oracle_username,1,8) "DB User"

,      substr(b.owner,1,8)           "Schema"

,      substr(b.object_name,1,20)    "Object"

,      substr(b.object_type,1,10)    "Type"

,      substr(c.segment_name,1,5)    "RBS"

,      substr(d.used_urec,1,12)      "# of Records"

, e.sid

from v$locked_object a

,    dba_objects b

,    dba_rollback_segs c

,    v$transaction d

,    v$session e

where a.object_id =  b.object_id

  and a.xidusn    =  c.segment_id

  and a.xidusn    =  d.xidusn

  and a.xidslot   =  d.xidslot

  and d.addr      =  e.taddr

/

comment : usefull to check sql for sid or locksql

select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');