Large index creation GOTCHA !!!

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     Received on Fri May 29 1998 - 09:29:17 CDT