Q

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.
This was first published in June 2007

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close