Re: Large index creation GOTCHA !!!

From: Chris Hamilton <toneczar_at_erols.com>
Date: Fri, 29 May 1998 16:40:22 GMT
Message-ID: <6kmof3$hvg$6@goo.nwd.usace.army.mil>

"Suresh Bhat" <suresh.bhat_at_mitchell-energy.com> wrote:

>Whenever an index is created by any Oracle user in a particular tablespace,
>it seems to me that SYS creates the index first in its default
>tablespace and then moves it to the designated tablespace.

> . . .

>EFM_HIST_IDX had reserved 70M under the segment_name 10.3 owned by OPS$XYZ.
>SCRATCH had several blocks under the same segment_name 10.3 owned by SYS.
>
>Then I got the message " unable to extend ..... in table space SCRATCH."

What is happening is that the index is SORTED first in the users designated TEMPORARY tablespace (see TEMPORARY_TABLESPACE in the DBA_USERS view), before it gets written to the actual index storage segment. The segment "10.3" owned by SYS is the actual sort occuring.

Chris

---

Chris Hamilton, DBA
US Army Corps of Engineers
christopher.h.hamilton_at_usace.army.mil
http://www.serve.com/cowpb/chamilton.html Received on Fri May 29 1998 - 11:40:22 CDT