Re: Why do I see multiple ST locks?

yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0106151315.5c2034b1_at_posting.google.com>...
> Oracle 8.0.5.0.0 Enterprise on Sparc Solaris 2.6. TEMP tablespace is
> type PERMANENT (which should be changed soon). At the moment of high
> transaction rate on this data warehouse, a SQLPlus screen shot (I did
> some editing only for cleaner display; SID 15 is SMON):
>
> SQL> select * from v$lock where type = 'ST';
>
> ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
>
> 791D1BAC 791D1BBC 15 ST 0 0 6 0 22 1
> 791D20D4 791D20E4 68 ST 0 0 0 6 22 0
>
> And after a while:
>
> ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
>
> 791D1BAC 791D1BBC 15 ST 0 0 6 0 14 1
> 791D1A08 791D1A18 27 ST 0 0 0 6 9 0
> 791D18A0 791D18B0 68 ST 0 0 0 6 12 0
> 791D1990 791D19A0 76 ST 0 0 0 6 9 0
> 791D1A44 791D1A54 159 ST 0 0 0 6 12 0
>
> I thought there should only be one ST enqueue in the entire database.
> So what's wrong?
>
> Yong Huang
> yong321_at_yahoo.com

Yong, only one session has the ST lock (per Lmode exclusive), everyone else is waiting for the single existing ST lock (per lmode =0 , request non-zero).

• Mark D Powell --