Ask the Expert

Best practice for tablespaces for large tables

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: