RE: Oracle9iR2 - temporary tablespace usage error

[Hitchman, Peter]

Hi,

There are a couple of issues here. Firstly you need to know that when you get the error about the "temp" segment when building the index, the segment in question is the index. It is just that when building the index Oracle creates a temp segment to sort the data in and to create the index in and when it finishes it changes the segment type to "index". But if it runs out of space trying to build the index, it reports the error about the segment without first changing the segment type.

 

When it comes to the temporary space in a tempfile temporary tablespace, Oracle never actually drops the extents that get allocated until the database is shutdown, instead they just get marked as being available when the session that first created them no longer needs them.

 

Regards

 

Pete

-----Original Message-----
From: laura pena [mailto:[EMAIL PROTECTED]
Sent: 05 September 2003 14:59
To: Multiple recipients of list ORACLE-L
Subject: Oracle9iR2 - temporary tablespace usage error

HI all,

 

I'm getting the following error when using temporary tablespace on a locally managed system tablespace. Seems if my system tablespace is locally managed I can not create a dictionary managed tablespace.

 

Can anyone let me know if it is a bad idea to have system tablespace locally managed and why I am getting the following the error on building an index:

 

ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01

 

Also, I issue the following query and looks like the tempoary tablespace is not being free'ed up( or maybe this is not what this v$ tables is telling me):

 

select tablespace_name, file_id,bytes_used,bytes_free
from v$temp_space_header;  

SQL> select tablespace_name,file_id,bytes_used,bytes_free
  2  from v$temp_space_header;

TABLESPACE_NAME                   FILE_ID BYTES_USED BYTES_FREE
------------------------------ ---------- ---------- ----------
TEMP01                                  2 2146435072          0

 

Thanks in advance.

Lizz

 

 

---

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software

__________________________________________________________________

The information contained in this email is confidential and
intended only for the use of the individual or entity named
above. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly
prohibited. Thomson Scientific will accept no responsibility
or liability in respect to this email other than to the addressee.
If you have received this communication in error, please
notify us immediately via email: [EMAIL PROTECTED]
__________________________________________________________________