Oracle: Moving indexes

Indexes, unless otherwise specified, will be generated in the same tablespace as the table it's indexing. Obviously, this is a bad idea due to OFA and general common sense. Presupposing that you already have a separate index tablespace, move the index from the DATA tablespace to the INDEX tablespace by doing the following:

alter index ${Index} rebuild

tablespace INDEXES

storage (initial 2M next 2M pctincrease 0);

Obviously, you'll want to munge the storage characteristics to something that makes sense for your environment.

In order to avoid having to do this everytime you create an table with a unique or primary key, specify where the indexes are supposed to go by:

create table ${table}

( ${column1} ${type},

  ${column2} ${type},

  constraint ${constraint_name} primary key ${colum1}

    using index tablespace INDEXES

    storage (initial 2M next 2M pctincrease 0)

  tablespace DATA

    storage (initial 5M next 5M pctincrease 0);