RE: Question about temporary segments

From: Wong, Bing <bing.wong_at_IngramMicro.com>
Date: Thu, 18 Oct 2001 14:42:37 -0700
Message-ID: <F001.003AF503.20011018144533@fatcity.com>

I would just increase that AWW_INDEX1 larger and then downsize it after rebuilt.

-----Original Message-----
Sent: Thursday, October 18, 2001 2:35 PM To: Multiple recipients of list ORACLE-L

>From Metalink Doc ID: 136644.996

During an index rebuild, there are two types of temporary segments involved.

First, there are the temporary segments that are used to store partial sort data when the SORT_AREA_SIZE is too small to process the complete sort set. These segments are built in the user's default TEMPORARY tablespace.

Second, as the index is being rebuilt, it uses a segment which is defined as a temporary segment until the rebuild is complete. Once this segment is fully populated, the old index can be dropped and this temporary segment is redefined as a permanent segment with the index name.

The error you are seeing is probably due to there being insufficient room in the index's tablespace to hold both the original index and the new version concurrently. The new version of the index, currently a temp segment, will be in the tablespace where the index is required.

HTH,
Michael Bush
Corporate Systems
Database Administration
(806) 337-3610
www.csedge.com

-----Original Message-----
Sent: Thursday, October 18, 2001 3:15 PM To: Multiple recipients of list ORACLE-L

Greetings All;
I am confused and hope someone can straighten me out.

My confusion surrounds temporary segments and where they are created. I had assumed that temporary segments were created in a user's, assigned temporary tablespace. However when I have a creation failure, for example, if I am attempting to rebuild an index that is currently in tablespace aww_index1, I received the following error: (ORA-1652: unable to extend temp segment by 1280 in tablespace AWW_INDEX1), this is telling me that the temp segment is being created in the tablespace that the permanent object exists in. Is this always the case? Is the only purpose of the user's, assigned "TEMPORARY" table space for sorting. Can I tell Oracle to redirect the creation of the temp segment to a different tablespace? If so how is that accomplished?

Thanks in advance.

Michael L. Petrus
GE Auto Warranty Services
7125 W. Jefferson Av. #200
Lakewood, CO 80235

Database Administrator

Phone: (303) 987 4129
Fax: (303) 987 4298
Email: Mike.Petrus2_at_gecapital.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Petrus, Mike (CAP, GEFA)
INET: Mike.Petrus2_at_gecapital.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Michael Bush
INET: mbush_at_csedge.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------