Thursday, 15 September 2016

How to detect locks in SQL sessions

To find out v$session holding lock:
SQL> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null;

To kill a locked session, first need to find sid, serial and use
SQL> alter system kill session 'sid, serial#';
*** you need have dba priviledge to kill sessions

 To find which SQL has lock wait:
SQL> select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece;

If #3 is a parameterized SQL, use V$SQL_BIND_CAPTURE to display information on bind variables used by SQL cursors. Each row in the view contains information for one bind variable defined in a cursor.
SQL> select * from V$SQL_BIND_CAPTURE where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value;

SQL to check deadlocks:
SQL> select    c.owner,    c.object_name,    c.object_type,    b.sid,    b.serial#,    b.status,    b.osuser,    b.machine from    v$locked_object a ,    v$session b,    dba_objects c where    b.sid = a.session_id and    a.object_id = c.object_id;