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

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!
>
> --
>
> Syltrem
> http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
> To reply to myself directly, remove .spammenot from my address
>
>
>
Received on Thu Feb 07 2002 - 20:12:16 CST