change tablespace for objects

[elaine3839]

After creating the db objects, how can I change the tablespace for them? Do I have to use Export/Import utility? Also, shall we always put data and indexes into different tablespaces for performance consideration?

Thanks a lot!

[marist89]

Yes, data and indexes should be put in seperate tablespaces.

In 8.1.x, you can use alter table xyz move tablespace new_tbs for tables and alter index xyz_pk rebuild tablespace new_tbs for indexes.

[julian]

Be careful when you define the PK of a table: if you do not include USING INDEX clause, Oracle will build it in the defualt tablespace which is often the DATA, not the INDEX tablespace.

Here is an example:

ALTER TABLE table_name
ADD CONSTRAINT pk_column_name PRIMARY KEY (sid)
USING INDEX
PCTFREE 10
INITRANS 4
MAXTRANS 255
TABLESPACE indx
STORAGE (FREELIST GROUPS 2 FREELISTS 4)
/