Re: enable/disable constraint, rebuild index

Hi

My research shows that if there is not enough space on the tablespace where the index is created, then the index will be rebuilt and migrated to the default tablespace of the user rebuilding the index.

I am not sure what Oracle uses to find this required space. It may be the contiguous initial extent for the index.

And I am not sure about this either, but if there is not enough space on either
tablespace, then index may not be rebuilt.

The correct command to be used to rebuild the index on the desired tablespace is:

ALTER INDEX index_name REBUILD TABLESPACE tablespace_name;

Same can be said of recreating primary keys after disabling.

But make sure that you have enough disk space for the index on SYS's default tablespace, because that is where it is sorted/rebuilt under the ownership of SYS and then moved to the indicated tablespace_name. The temporary index built by SYS has name like 10.6 etc. which is something like file_id.block_id.

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Winnie Liu <poohland_at_hotmail.com> wrote in article <6rt3kg$5sm_at_dfw-ixnews9.ix.netcom.com>...
> Hi everyone,
>
> I learn recently that when you disable the primary key and unique key
> constraint. The indexes associated with it are gone. When you enable the
> constraint, Oracle will build the index on the user's default tablespace
> instead the of the tablespace that you specific when you create the
primary
> key/unique constraint.
>
> How about rebuilding index? Is it going to the user default or the
> tablespace that index resign in? (I really hope that it is the second
one...
> I always think that it will go to the tablespace which I create the index
> instead of the default tablespace! =<)
>
> Thanks everyone!
>
> Winnie
>
>
>
Received on Wed Aug 26 1998 - 15:42:29 CDT