Re: URGENT! ORA-1658 in TEMP tablespace???

"Syltrem" <syltrem_at_videotron.pas.de.spam.ca> wrote in message news:<QuG88.10335$Fj7.380940_at_wagner.videotron.net>...
> NOT so urgent anymore.
>
> I found that user`s temp tablespace can be changed dynamically (user does
> not need to log back in). That`s a good thing; I just created a new
> tablespace, assigned the users to it and all was fine.
>
> But why did this happen? I read an article on metalink that suggests the
> space used in a temp tablespace may not be free for 12 hours! (in 7.x)
>
> I am on 8.1.6.0
>
> Before this happened, I did a big CREATE INDEX that completed successfully.
> Then I started to do another one that completed with an error allocating
> temp segment in the index`tablespace (bacause reached maxextents). I used
> the wrong tablespace - my fault.
>
> Then the temp tablespace was full, and Oracle would not free it. Seems like
> a bug to me.
>
> If someone knows, please tell me!
>
> "Syltrem" <syltrem_at_videotron.spammenot.ca> a écrit dans le message de news:
> RGC88.4049$EI.21844_at_tor-nn1.netcom.ca...
> > How is this possible?
> > How can I clear the situation?
> >
> > SQL> select BLOCKS from v$sort_usage;
> >
> > BLOCKS
> > ----------
> > 260
> > 260
> > 260
> >
> > 3 rows selected.
> >
> > and I get
> > ORA-01658: unable to create INITIAL extent for segment in tablespace TEMP
> >
> > If I reduce significanly the size of initial/next extent in tablespace
> temp
> > I get
> > ORA-01652: unable to extend temp segment by 2 in tablespace TEMP
> >
> >
> > SQL> select TABLESPACE_NAME, INITIAL_EXTENT, CONTENTS from dba_tablespaces
> > where
> > tablespace_name='TEMP';
> > TABLESPACE_NAME INITIAL_EXTENT CONTENTS
> > ------------------------------ -------------- ---------
> > TEMP 16384 TEMPORARY
> >
> >
> > Thanks!
> >

With a tablespace of type temporary only one sort segment is allocated (see v$sort_segment) per, user asigned, temporary tablespace on first use. It is never freed but the extents within it should be available for re-use as soon as the sessions using them finish with them. I have read some metalink (Oracle support) forum posts reporting problems where temp tablespace created using create temporary tablespace temp fail to release the extents for reuse where the connection is coming from a web server and is not closed.

I notice that your extent size is very small. It should be at least as large as your sort_area_size and probably should be a multiple there of. (Technically to allow for overhead it should actually be, I think, 1 block bigger)

HTH

• Mark D Powell --