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
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.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

,      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 »