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

Thanks for the info.

> 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.
Apparently Oracle did not release the extents, even after I killed the session and made sure the process is really gone on the OS side as well as the Oracle side.

> 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
My actual segment size is bigger. I just tried to get is small enough so that Oracle would not complain it cannot create initial sehgment (but that failed).

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to myself directly, remove .spammenot from my address

"Mark D Powell" <mark.powell_at_eds.com> a écrit dans le message de news:
178d2795.0202080734.793b4b9d_at_posting.google.com...


> "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 --