Sooner or later, every DBA will need to address how to manage storage capacity in their databases. Nearly every database needs more storage as time goes on, and without attention, the database will fill up, and new data/inserts can’t happen. It’s been my experience that bad space mangement is the root cause of a high percentage of database downtime incidents.

In the Oracle RDBMS, every segment (table/table partition, index/index partition, queue) must be assigned to a particular tablespace. Each tablespace is composed of one or more datafiles. Datafiles do have a maximum size, depending on the blocksize for traditional smallfile tablespaces; for a tablespace with a block size of 8k, the maximum size of a datafile is just under 32Gb. So, each tablespace is typically comprised of many datafiles. Oracle has several tablespaces that are built-in; these include the SYSTEM, SYSAUX, TEMP, and UNDO/ROLLBACK tablespaces.

Let’s say that you wanted to make the work of the next DBA who manages a database you control as hard as possible. One of the easiest ways to do this is to create a database with LOTS of tablespaces; you could even create 1 tablespace per segment. This means that instead of worrying about one ‘pool’ of storage, the next DBA will need to manage dozens, or hundreds, of storage pools. Because this management is mission-critical (if ANY tablespace fills up, your application may not work), it is far more likely that one will be overlooked, and fill up. Using lots of tablespaces will also make it far more likely that a lot of space is wasted in each tablespace, because every tablespace will need to maintain a certain amt of free space to be ’safe’ from future increases in growth.  If a segment is dropped or shrunk, the space released will be only in that one tablespace, and won’t be able to be used by segments assigned to all of the other tablespaces.

If you wanted to make the the work of the next DBA who manages a database you control the easiest, you’d create just one tablespace (which could be a BIGFILE tablespace, which can use larger files than SMALLFILE tablespaces), and stick all of your segments into there. There would only be one tablespace to manage; when segments are shrunk or removed in the course of business, that space can immediately be used by any other segment. To manage extent sizes, you could simply use the EXTENT MANAGEMENT LOCAL AUTOALLOCATE feature, which automatically gives you near-to-optimimum sizes.

Prior to Oracle 10g, there were many legitimate reasons to use multiple tablespaces and there still are good reasons to segregate your segments into separate tablespaces. Some of these reasons are:

• You may need different block sizes for different segments for best performance; since each tablespace has one block size, to use different ones you need different tablespaces.
• If you aren’t using a SAN, or ASM, or similar storage solution, you may wish to segregate segments on differnet LUNs/drives for better performance. Given the size of today’s databases, and the need for high IOPS storage solutions, most non-trivial databases are using SANs/ASM/other storage solutions these days. Using these, it doesn’t give you better performance by segregating tablespaces.
• If you need to use the ‘transportable tablespaces’ feature to move large quantities of data between Oracle databases,
• If you use Oracle’s direct-path load feature, which loads data above the high-water mark of each datafile directly for faster data loading (by bypassing the internal SQL engine), then you may wish to continually create new tablespaces for this purpose.
• If you have several separate users in your database, and wish to ’split’ the database in the future so that you have one databases each for each user, then you may wish to create a dedicated tablespace for each user. This would allow you to clone half of the database using RMAN on a new machine, and OFFLINE DROP the datafiles you do not want.

In conclusion, one easy way to make your oracle database use space more efficiently, be easier to manage, and therefore be less likely to fill up, is to use fewer, larger application tablespaces, rather than more.