Lock Script

Hii All

 Today i'm posting  lock script. sometime some session block to other session. This situation is very critical because our whole system is impacted. so this script is very helpful to us.

select s1.username || '@' || s1.machine
   || ' ( SID=' || s1.sid || ' )  is blocking '
     || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
       from v$lock l1, v$session s1, v$lock l2, v$session s2
      where s1.sid=l1.sid and s2.sid=l2.sid
      and l1.BLOCK=1 and l2.request > 0
       and l1.id1 = l2.id1
      and l2.id2 = l2.id2 ;

Note-- The above script inform us to locking detail and sid detail.

select  p.spid,s.sid,s.serial#,s.p1,s.p1text,s.username,s.status,s.last_call_et,p.program,p.terminal,s.logon_time,s.module,s.osuser,l.SQL_TEXT,w.event,w.SECONDS_IN_WAIT
from V$process p,V$session s,v$sql l,v$session_wait w
where s.paddr = p.addr
and s.sid=w.sid
and l.SQL_ID=s.SQL_ID
and l.HASH_VALUE=s.SQL_HASH_VALUE
and l.ADDRESS=s.SQL_ADDRESS
and s.sid=&SID
order by  SECONDS_IN_WAIT;


Note- The above script inform us to sql detail.


If you want to grep more detail corresponding this session you can check with the help of  v$session. From this command  we can  check long running sql.

If suppose session is idle long time you can kill the session also. After that lock is automatically cleared.

SQL> Alter system kill session 'Sid&serial';