Re: What row and table are locked

Mike Moritz wrote:
>
> I am running Oracle Server 7.1.4.1.0
>
> Can someone help me determine what rows of what tables users are
> locking? If I am way off base here, does anyone have some scripts
> that will provide the desired info.
>
> I've figured out how to determine which users have locks but am
> not able to determine what table and row they are locking.
>
> I think the solution lies in the ability to decode the *ADDR
> fields of v$_lock. Does one of the *ADDR point to a ROWID?
> Am I close?
>
> Can anyone tell me what these fields point to and how to decode
> them?
>
> SQL> describe v$_lock;
> Name Null? Type
> ---------------- -------- ----
> LADDR RAW(4)
> KADDR RAW(4)
> SADDR RAW(4)
> RADDR RAW(4)
> LMODE NUMBER
> REQUEST NUMBER
>

For me the easier way to see what row is locked, who lock and who is locked is:

select oracle_username from v$locked_object where xidusn is not null;

Return who lock;

select v$session.username, v$session.row_wait_row#, v$sesion.row_wait_file#, v$session.row_wait_block#, all_objects.object_name from v$session,
all_objects where all_objects.object_id = v$session.row_wait_obj#;

Return who is locked and what object is locked.

And the rowid of the lock row is:

row_wait_block#, row_wait_row#, row_wait_file# (you must change the format only);

Hope this help:

                        Elisa Received on Mon Mar 24 1997 - 00:00:00 CST