Re: locks

From: wegorz <wegorz_at_winuel.com.pl>
Date: Tue, 28 Nov 2000 12:28:25 +0100
Message-ID: <9004jk$gaf$1@news.tpi.pl>

Try this one:

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

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

column sid heading 'Session|ID'
column username heading 'Username' format a10 truncate column image heading 'Active Img' format a10 truncate

select

        c.sid,
        substr(object_name,1,10) OBJECT,
        c.username,
        substr(c.program,length(c.program)-10,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',           /* NOT */
                2, 'Row-SELECT (SS)',     /* LIKE */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* SELECT */
                5, 'SELECT/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(b.lmode)) mode_held,
         decode(b.request,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* NOT */
                2, 'Row-SELECT (SS)',     /* LIKE */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* SELECT */
                5, 'SELECT/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');

Andrew Rajczakowski Received on Tue Nov 28 2000 - 05:28:25 CST