SUMMARY : Event to disable SMON from freeing TEMP

   What I observed as SMON holding the ST enqueue is wrong. When SMON is cleaning TEMP, it does not service any 'sort segment requests' and that's what I observed. I did some testing and obtained some interesting results.

Workarounds

1. Alter the TEMPORARY tablespace for all users to another PERMANENT tablespace. Sorts using the PERMANENT temporary tablespace do get blocked.
2. Alter the TEMPORARY temp tablespace to PERMANENT and shutdown abort the database. On startup, sorts using the temp tablespace get processed as usual.

Thanks,
Denny

Denny Koovakattu wrote:
>
> Correction, smon is holding the ST ( space management ) lock and other
> sessions are wait for it.
>
> Thanks,
> Denny
>
> Denny Koovakattu wrote:
> >
> > Hi,
> >
> > Platform : HP-UX 11.0 (64 bit)
> > Oracle : 8.0.5.1 (64 bit)
> >
> > I think this was discussed before but I can't find the details in my
> > notes or archives. I have a 1TB+ database with TEMP ( set to temporary )
> > having approx. 10000 extents. After bouncing the database SMON is
> > freeing up the extents and is holding the TS lock. ( v$lock shows SMON
> > holding the CI lock, but other sessions are waiting on the TS lock. ) Is
> > there an event that can be set to force SMON to release the lock after
> > 'n' iterations ?
> >
> > Thanks,
> > Denny

-- 
Denny Koovakattu
http://vitalsol.com/

__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.