Single vs Multiple tablespaces

From: M Hand <>
Date: Wed, 29 Jul 2009 10:38:09 -0400
Message-ID: <>


The LMT autoallocate vs uniform extent thread got me thinking on another general layout question that has been bugging me for a while. That of a single tablespace (meaning database creation generated tablespaces, plus UNDO, plus TEMP, and one for all application segments) or multiple tablespaces (application segments spread across several or several dozen). The single tablespace layout is something a recall SAP suggesting at one point.

In the old days, one of the arguments for multiple tablespaces was to balance disk I/O, most often implemented by separating a table and its indices. RAID, SAN and similar technologies have invalidated this reasoning. Another of the old-time reasoning was the limitations of datafile size and quantity per tablespace and I believe there was a upper limit of the number of segments per tablespace.

Backup & recovery seems to be the last good argument for keeping application data in multiple tablespaces, yet many 3rd party apps & data are so complex & interdependent (I'm thinking SAP here), that a TS PITR would leave you at great risk of inconsistent data.

So, I'm interested to know if anyone is using a single application tablespace layout? Any regrets?

OK, I've left out topics like tablespace migration, but I was thinking along the lines of a generic Oracle database (if there is such a thing).


Mike Hand

Received on Wed Jul 29 2009 - 09:38:09 CDT

Original text of this message