TableSpace

[r_adusumil]

Hi All,
I have created an instance and all the tables in the instance are in one table space and i would like to move them to different tablespace modulewise.
Will moving of tables into several tablespaces increasse the performance or its only advantageous while maintenance,
please explain.
Thanks

[tamilselvan]

It is a good idea to create multiple tablespace for different segments.
SYSTEM for data dictionary tables
RBS for rollback segments
Temp for temporary segments
DATA for users' data
INDX for indexes

Not only management of tablespaces is easy but also you will get performance.

You can use "MOVE" option of ALTER Table ...and of ALTER INDEX to move objects from one tablespace to another.

[marist89]

You will get better performance if your tablespaces are spread out on multiple devices. If all your data tablespaces are on one physical device, you will gain minimal performance advantage.

[mrvajrala]

Its always good idea to have more number of tablespaces ( instead of just one for all tables ), will be useful for manageability and.... if you are able to keep the data files of different tablespaces on different disks with individual disk controllers will certainly improve performance by reducing the DISK I/O...

And nother thing is you can distinguish some of the tables which are read-only by nature and keep them in a separate tablespace and make the tablespace is read only...

And if you have all tablespaces in a single disk with only one controller, it may not be a big help in perfromance , except the manageablity/modularity...

Thanks,
Madhu