Re: v$lock columns, info about rollback slot??

From: Manuela Mueller <520040906697-0001_at_t-online.de>
Date: Thu, 20 Dec 2001 00:13:54 +0100
Message-ID: <3C211F32.BE9A7DA8@t-online.de>

Hello Lubomir,
this was exactly my first idea yesterday evening. My first interpretation of v$lock was: this dynamic view give me info about currently hold locks, the ID of the locked objects and locked mode. First Interpretation of v$locked_object was: more detailed info about the locked object, including undo segment number, slot number etc... What made me insecure was the difference on my interpretation with two old Oracle exam questions.
The question about v$lock was in my first post. The second one: Why would you query v$locked_object? Correct answer: to determine the ID of the object for a lock currently held.

In the test situation user sys queried v$lock and v$locked_object:

select * from v$locked_object;

output:

    XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID orcl_user os_user
---------- ---------- ---------- ---------- ---------- ------------

---

PROCESS LOCKED_MODE
--------- -----------

         2 32 1232 2960 9 MANU test62

4632               
5                                                      

for v$lock:
select s.username, s.sid, s.serial#, l.id1, l.id2, l.lmode, l.type, l.ctime, l.addr, l.kaddr
  from v$session s, v$lock l
 where s.sid = l.sid
   and s.username is not null;

output:
USERNA SID SERIAL# ID1 ID2 LMODE TY CTIME
------ ---------- ---------- ---------- ---------- ---------- --

---

ADDR KADDR
-------- --------
MANU 9 63 2960 0 5 TM 13<--TM, before commit
51DC7E5C 51DC7E70  

MANU            9         63     131104       1232          6
TX          0<--TX, AFTER COMMIT, now reference in rollback

0958692C 095869F8

The results of the test situation I created today let me live with this two answers.
As Stephan pointed out, when the update is not comitted, I can only see a TM lock (This I what I expected). But if the commit is completed, I find additionally a TX entry with an ID value that matches the sequence number.
Thanks for your input
Manuela Mueller

Lubomir Petrov wrote:

> Hi Manuela,
>
> I think v$locked_object comes here... You can get the session id from
> v$lock and then query v$locked_object to get the additional info.
>
> SQL>desc v$locked_object
> Name Null? Type
> ------------------------ -------- -----------------------
> XIDUSN NUMBER (Undo segment number)
> XIDSLOT NUMBER (Slot number)
> XIDSQN NUMBER (Sequence number)
> OBJECT_ID NUMBER
> SESSION_ID NUMBER
> ORACLE_USERNAME VARCHAR2(30)
> OS_USER_NAME VARCHAR2(30)
> PROCESS VARCHAR2(9)
> LOCKED_MODE NUMBER (Lock mode)
>
> HTH
> Lyubomir Petrov
Received on Wed Dec 19 2001 - 17:13:54 CST