Re: who is locking what

From: Youri N. Podchosov <ynp_at_ynp.dialup.access.net>
Date: 1995/06/23
Message-ID: <1995Jun23.212853_at_ynp.dialup.access.net>#1/1

In article <DAIstD.BxB_at_inter.NL.net>, E.W.Vergragt_at_inter.nl.net (Edward W. Vergragt) writes:

|> Oracle locks a record, the moment you attempt to update it. Is there
|> any way (eg. via v$-tables, rowid?) to find out from another logged-on
|> user, WHO is locking the record.
|> 
|> We want such a feature to complete our SQL*Forms lookalike product,
|> built in C.
|> 

Some time ago, the following SQL script has been posted here at c.d.o.:

---

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!

---

+-----------------------------------------------------------------------------+

| Youri N. Podchosov (ynp) *** Davidsohn & Son, Inc. NYC *** 718-234-4140 | | Internet: ynp_at_ynp.dialup.access.net CIS: 72723,2202 AOL: ynp, yourip |

+-----------------------------------------------------------------------------+

Received on Fri Jun 23 1995 - 00:00:00 CEST