Re: Question about temporary segments

Mike,

When it creates an index, Oracle doesn't make it visible as it's being built. So

basically, Oracle creates the index as a temporary segment in the location it will belong when it's done being created. When Oracle finishes creating the index, Oracle then makes the index 'un-temporary', gives it its specified index name, and leaves it where it is.

The alternative would be to create the index in TEMP and then take the extra step of moving the index to its final destination. Oracle doesn't do that.

Note that these are different temp segments than the ones Oracle uses to sort the data in order to build the index. Those are, as you noted, stored in TEMP.

HTH, Yosi

"Petrus, Mike (CAP, GEFA)" wrote:

> 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: Yosi Greenfield
  INET: yosi_at_comhill.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).