Re: Large index creation GOTCHA !!!

Check to see what SYS's temporary tablespace is. Oracle uses the temporary tablespace for the sorting that is needed to build the index. You will probally find that SYS's temporary tablespace is SCRATCH too.

In article <01bd8b1d$2da2d340$a504fa80_at_mndnet>,   "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.
>
> Here is what happened:
>
> I was creating an 150M index with the following statement in SQL*Plus under
> OPS$XYZ,
> which has also the DBA privileges --
>
> create index
> OPS$pXYZ.hist_alarm_ndx1
> on OPS$XYZ.hist_alarm ( meter_id, create_date desc, alarm_date desc )
> tablespace EFM_HIST_IDX
> storage( initial 70M next 20M pctincrease 0 )
> pctfree 5
> /
>
> SYS's default tablespace is SCRATCH. Entire SCRATCH tablespace is 125M.
>
> EFM_HIST_IDX tablespace had about 190M freespace,
> with 2 big chunks of 83M contiguous freespace.
>
> Index creation took about 10 minutes.
>
> I monitored what was being generated in EFM_HIST_IDX and SCRATCH
> tablespaces using
> dba_free_space and dba_extents views/tables.
>
> Here is what I noticed:
>
> 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."
>
> Of course I resized the SCRATCH to 200M and it worked.
>
> The Question :
>
> Is this the way index creation supposed to work or is there something
> hidden that I am missing.
>
> Thanks in advance for your input and time
>
> suresh.bhat_at_mitchell-energy.com
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri May 29 1998 - 16:25:32 CDT