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!
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.
"I pledge to stop eating sharks fin soup and will not do so under any circumstances."
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)
STORAGE (FREELIST GROUPS 2 FREELISTS 4)