Manage Learn to apply best practices and optimize your operations.

Best practice for tablespaces for large tables

What is the best practice for large tables in a database -- separate tablespaces for each big table or put all big tables in one tablespace?

Our tablespaces are locally managed. Data and index have separate tablespaces on separate disks. Some tables in the DB are quite large. What is the best practice for such large tables when a database would be created again? I've been going through Metalink and the Web but can't find any specific info on this, e.g., separate tablespaces for each big table or put all big tables in one tablespace? All other small tables in separate tablespace as well? Or specify for each of these tables a uniform (large) extent size. Any detail if calculation is required, e.g., which uniform extent size when using uniform size, would be welcome. DB size 150 GB. About 10 tables/indexes are between 10 and 20 GB.
There are many DBAs who like to put their large tables into their own tablespace. The school of thought is to put small tables in one tablespace, medium tables in another, and large tables in a third tablespace. This way, you can manage the storage characteriscs of these three table size categories individually. For instance, you might want to use a larger block size for your large tables. The other school of thought is to separate your tables based on usage patterns and application usage.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.