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!
Printable View
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.
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)
/