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

Hi Manuela,

I didn't think about ID1 and ID2 columns, because I already knew the info is in V$LOCKED_OBJECT. But it turned that you can get a lot more information from V$LOCK, especially from ID1 and ID2 (and I think the first OCP question is about that).

Resource Identifier ID1:
- For DML locks, ID1 is the object_id from DBA_OBJECTS (as Stephan pointed out).
- For TX locks, ID1 points to the rollback segment and transaction table entry
(- For TX locks, ID2 obviously is the sequence number)

Now about ID1 in the case of TX lock: "ID1 points to the rollback segment and transaction table entry". Two pieces of information in one number.

The manual states, that you can find the rollback segment number in the following way:

select trunc(ID1/65536) from V$LOCK;

This means that the transaction table entry is in the lower 16 bits and the rollback segment number (USN) is in the higher bits.

You can find more information in "Oracle Database Performance Guide and Reference", Chapter 24 "Dynamic Performance Views for Tuning"

Regards
Lyubomir Petrov

Manuela Mueller <520040906697-0001_at_t-online.de> wrote in message news:<3C211F32.BE9A7DA8_at_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 Thu Dec 20 2001 - 00:35:27 CST