Script to check Locking and Blocking in oracle database

#Locking#
===========

select session_id "sid",SERIAL#  "Serial",
    substr(object_name,1,20) "Object",
      substr(os_user_name,1,10) "Terminal",
      substr(oracle_username,1,10) "Locker",
      nvl(lockwait,'active') "Wait",
      decode(locked_mode,
        2, 'row share',
        3, 'row exclusive',
        4, 'share',
        5, 'share row exclusive',
        6, 'exclusive',  'unknown') "Lockmode",
      OBJECT_TYPE "Type"
    FROM
      SYS.V_$LOCKED_OBJECT A,
      SYS.ALL_OBJECTS B,
      SYS.V_$SESSION c
    WHERE
      A.OBJECT_ID = B.OBJECT_ID AND
      C.SID = A.SESSION_ID
    ORDER BY 1 ASC, 5 Desc;

##Blocking##
=============

select 'SID '||l1.sid||'--'||s1.username || ' IS BLOCKING SID ' || l2.sid||'--'||s2.username blocking_report from v$lock l1, v$lock l2,v$session s1,v$session s2 where
l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2 and s1.sid=l1.sid and s2.sid=l2.sid;


#Blocking_count#
=================

select count(*) from   v$lock a, v$session s, all_objects ob, v$locked_object lo where  s.sid = a.sid and lo.session_id = a.sid and ob.object_id = lo.object_id and  a.block = 1


## LOCKED_OBJ ##
=================


select o.object_name as "object_name",
       s.sid as "sid",
       s.serial# as "serial#",
       s.username as "username",
       sq.sql_fulltext as "sql_fulltext"
  from v$locked_object l, dba_objects o, v$session s,
       v$process p, v$sql sq
  where l.object_id = o.object_id
    and l.session_id = s.sid and s.paddr = p.addr
    and s.sql_address = sq.address;


Session Using Table :
======================

SELECT * FROM v$lock WHERE id1 = (SELECT object_id FROM all_objects WHERE owner ='owner_name' AND object_name ='table_name');

SELECT * FROM v$session WHERE sid =<above query result>


TO FIND SQL TEXT FROM SID:
============================

select a.sid,a.program,b.sql_text from gv$session a, v$sqltext b where a.sql_hash_value = b.hash_value and a.sid=68 order by a.sid,hash_value,piece;


Find SID from PID
=================

col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
/


select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser
from V$process p,V$session s
where s.paddr = p.addr and s.status = 'ACTIVE' and s.username not like '%SYS%';
/


## SID_Checks ##
 ==============
set lines 300
col OSUSER for a15
col PROGRAM for a30
col MACHINE for a25
col SQL_ID for a20
col STATUS for a15
col username for a20

select sid,serial#,sql_id,program,username,osuser,state,status,logon_time from gv$session where sid ='&1';

select sql_text from v$sql where sql_id='&1';


select sid, serial#, username, status, osuser, machine, terminal, LOGON_TIME
from v$session
where status='INACTIVE' --and logon_time < sysdate-1
order by logon_time ;


Get SID from PID :
==============

select sid,serial#,sql_id,program,username,osuser,state,status,logon_time from gv$session where paddr in (select addr from v$process where spid ='50678');


//
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

//
Enq: TX - row lock contention Issue
==============================


## The “enq: TX - row lock contention” event often indicates an application level locking problem. The TX enqueue is the transaction enqueue (a.k.a. enq: TX – contention) and can also be related to buffer busy waits, in conditions where multiple transaction attempt to update the same data blocks. TX enqueue is issued when a transaction makes its first change, and released when the transaction performs a COMMIT or ROLLBACK.

To troubleshoot the wait event “enq: TX – row lock contention”, use the following SQL:

 For which SQL is currently waiting on:

select
    sid,
    sql_text
from
    v$session s,
    v$sql q
where
    sid in
    (select
       sid
    from
       v$session
   where
       state in ('WAITING')
   and
       wait_class != 'Idle'
   and
       event='enq: TX - row lock contention'
   and
      (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

The blocking session is:
====================

select
    blocking_session,
    sid,    serial#,
    wait_class,
    seconds_in_wait
from
    v$session
where
    blocking_session is not NULL
order by
    blocking_session;