Re: Determining Locked user??

barry.roomberg_at_compudata.com (BARRY ROOMBERG) writes:

>-> Is there any query I can use to determine which user is locking a
>-> particular row? I cannot seem to find anything about this in the
>-> manauls. If you give me the query and/or point me in the right
>-> direction...
 

>In Oracle 6 I had a query that would show who is holding/waiting
>for certain locks, which I would relate to tables (not rows).
 

>Those data dictionary views went away in O7, so I'm looking for
>the same thing. Please post any response/solution you get.

At least in version 7.1.3 this does not seem to be possible. A row level lock obtained as the result of a "select... for update" shows up in the sys.v$lock table as a TX (transaction enqueue) lock. The associated data points to a block in a rollback segment (it's possible to determine the name of the rollback segment bug so what?). There does not appear to be anyway to identify the "source" table. For TM locks this is possible. Here's the basic SQL statement I've got embedded in a Pro*C program which monitors for lock conflicts:

        select nvl (s.username, '?'), nvl (s.osuser, '?'),
            s.sid, nvl (s.process, -1), nvl (s.terminal, '?'),
            l.type, l.id1, l.id2, l.lmode, l.request,
            nvl (u.name, '?') || '.' || nvl (o.name, '?')
        from v$session s, v$lock l, sys.obj$ o, sys.user$ u
        where l.sid = s.sid and l.id1 = o.obj# (+)
            and o.owner# = u.user# (+)
            and l.type in ('RW', 'TM', 'UL', 'TX')
        order by l.id1, l.id2, l.request desc, l.lmode desc

The complete source for a curses based application that uses this information to alert sysadmin/dba staff of a locking problem is available for a nominal fee. Here is an example of what is displayed:

    Fri Nov 25 11:20:30 1994
    Oracle User Name SID Login PID Idle Type Mode Held/Requested

    APPDEMO.TESTLOCK

    APPDEMO             9 joeb       3280  0:11 TM   Exclusive Requested
    APPDEMO            10 suzieq     3248  0:39 TM   Row Share Held
    APPDEMO            42 arnold     3251  0:33 TM   Row Share Held

    ?.?
    APPDEMO            42 arnold     3251  0:33 TX   Exclusive Requested
    APPDEMO            10 suzieq     3248  0:39 TX   Exclusive Held

-- 
Kurtis D. Rader, Sr. Technical Consultant       voice: 503/578-3714
Sequent Computer Systems                          fax: 503/578-5453
15450 SW Koll Parkway, M/S WIL1-541              UUCP: ...uunet!sequent!krader
Beaverton, OR  97006-6063                    Internet: krader_at_sequent.com