Showing Locked Objects

From: Alan Schafer <bschafer_at_ocvaxc.cc.oberlin.edu>
Date: 5 Aug 1994 19:33:45 GMT
Message-ID: <31u46p$pso_at_news.cc.oberlin.edu>

Here is a neat script that will show locked objects (tables) in the database as well as who is locking them.

set linesize 132
set pagesize 60
column object heading 'Database|Object' format a15 truncate

column lock_type heading 'Lock|Type' format a4 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a15 truncate

column sid heading 'Session|ID'
column username heading 'Username' format a20 truncate column image heading 'Active Image' format a20 truncate select

        c.sid,
        substr(object_name,1,20) OBJECT,
        c.username,
        substr(c.program,length(c.program)-20,length(c.program)) image,
        decode(b.type,


                'MR', 'Media Recovery',

                'RT', 'Redo Thread',

                'UN', 'User Name',

                'TX', 'Transaction',

                'TM', 'DML',

                'UL', 'PL/SQL User Lock',

                'DX', 'Distributed Xaction',

                'CF', 'Control File',

                'IS', 'Instance State',

                'FS', 'File Set',

                'IR', 'Instance Recovery',

                'ST', 'Disk Space Transaction',

                'TS', 'Temp Segment',

                'IV', 'Library Cache Invalidation',

                'LS', 'Log Start or Switch',

                'RW', 'Row Wait',

                'SQ', 'Sequence Number',

                'TE', 'Extend Table',


                'TT', 'Temp Table',


                b.type) lock_type,
        decode(b.lmode,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(b.lmode)) mode_held,
         decode(b.request,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(b.request)) mode_requested

from sys.dba_objects a, sys.v_$lock b, sys.v_$session c where a.object_id = b.id1 and b.sid = c.sid and owner not in ('SYS','SYSTEM');

Enjoy! Received on Fri Aug 05 1994 - 21:33:45 CEST