Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for the ‘Admin Scripts’ Category

check for Locked Sessions

Posted by Hendry chinnapparaj on May 17, 2010

REM check_lock.sql

set linesize 500
SET PAGESIZE 500
col waiting_session format 99999 heading ‘Waiting|Session’
col holding_session format 99999 heading ‘Holding|Session’
col mode_held format a20 heading ‘Mode|Held’
col mode_requested format a20 heading ‘Mode|Requested’
col lock_type format a20 heading ‘Lock|Type’
prompt blocked objects from V$LOCK and SYS.OBJ$

set lines 132
col BLOCKED_OBJ format a35 trunc

select /*+ ORDERED */
    l.sid
,   l.lmode
,   TRUNC(l.ctime/60) min_blocked
,   u.name||’.’||o.NAME blocked_obj
from (select *
      from v$lock
      where type=’TM’
      and sid in (select sid
                  from v$lock
                  where block!=0)) l
,     sys.obj$ o
,     sys.user$ u
where o.obj# = l.ID1
and   o.OWNER# = u.user#
;

prompt blocked sessions from V$LOCK

select /*+ ORDERED */
   blocker.sid blocker_sid
,  blocked.sid blocked_sid
,  TRUNC(blocked.ctime/60) min_blocked
,  blocked.request
from (select *
      from v$lock
      where block != 0
      and type = ‘TX’) blocker
,    v$lock        blocked
where blocked.type=’TX’
and blocked.block = 0
and blocked.id1 = blocker.id1
;
prompt blockers session details from V$SESSION

set lines 132
col username format a10 trunc
col osuser format a12 trunc
col machine format a15 trunc
col process format a15 trunc
col action format a50 trunc

SELECT sid
,      serial#
,      username
,      osuser
,      machine
FROM v$session
WHERE sid IN (select sid
      from v$lock
      where block != 0
      and type = ‘TX’)
;

Posted in Admin Scripts | Leave a Comment »