Locked Object SQL

From: TSAWMILL.US.ORACLE.COM <TSAWMILL_at_US.ORACLE.COM>
Date: Mon, 22 Jan 1996 09:13:04 -0800
Message-Id: <9601221743.AA30938@alice.jcc.com>

--Boundary-2883606-0-0

Attached is a script that was posted some time ago to this list that shows objects' lock status and the user doing the locking.

Tim Sawmiller
tsawmiller_at_us.oracle.com
"The opinions expressed here are my own and not necessarily those of the Oracle Corporation".

--Boundary-2883606-0-0
Content-Type: message/rfc822

Date: 22 Jan 96 08:14:42
From:"SAWMILLERT_at_detroitedison.com" <SAWMILLERT_at_detroitedison.com> To: tsawmill_at_us.oracle.com
Subject: lockobj.sql

set linesize 1000
set pagesize 60

column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a35 truncate

column object heading 'Database|Object' format a25 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 a10 truncate

column sid heading 'SID'
column username heading 'Oracle|Username' format a7 truncate column image heading 'Active Image' format a20 truncate column sid format 99999
select

--      c.program,
        c.osuser,
--      c.machine,
--      c.process,
        c.sid,
        a.owner||'.'||a.object_name object,
        c.username,
        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')
/

--Boundary-2883606-0-0-- Received on Mon Jan 22 1996 - 12:43:46 CST