Oracle Tablespaces and Datafiles Overview

A tablespace is the primary level of logical storage in an Oracle database. All "hard" database objects such as tables, indexes, sequences, and clusters reside in tablespaces.

An Oracle database can have numerous tablespaces. The amount depends on the limit imposed by the specific operating system of open files for a given process (assuming one datafile per tablespace and subtracting for redo logs).

A tablespace must have at least one datafile. Datafiles are the physical storage for an Oracle database. With the exception of redo logs and control files all data in an Oracle database is kept in datafiles.

Objects that perform similar functions and behave in similar ways should be placed in the same tablespace. For example, all lookup tables should be placed in a separate tablespace from interactive tables which should be in a separate tablespace from indexes which should be in a separate tablespace from rollback segments.

Oracle performs best if it is widely distributed. The physical datafiles should be spread across as many disks as is possible. The actual number of datafiles that can be used by an Oracle instance is set at database creation with the MAXDATAFILES parameter in the CREATE DATABASE command. The number of datafiles allowed also is dependent upon the size of the operating system blocks.

The number of potential applications will drive the number and size of database tablespaces above and beyond the five base tablespaces. These are:

• SYSTEM. – Contains files owned by the SYS and SYSTEM user.
   
• TOOLS. – Contains files usually owned by SYSTEM but that apply to the Oracle developer’s toolset; these files contain base information and details of forms, reports, and menus.
   
• ROLLBACK – Contains the private rollback segments; its size will depend on number of rollback segments and expected transaction size.
   
• DEFAULT USER – Tablespace in which users can create and destroy temporary nonapplication-related tables such as those used in SQL*REPORT for intermediate queries.
   
• TEMPORARY USER. – Tablespace for sorts, joins and other operations that require temporary disk space for intermediate operations. If this tablespace is not available and default tablespace is not set for each user, these tables will be created and dropped in the SYSTEM tablespace, resulting in fragmentation. Additionally, a poorly designed join or overly ambitious SELECT statement could result in filling the SYSTEM area and halting the database.

Each application should have its own tablespace. If there are several small applications, you might want to put them in a single large tablespace, but if you can avoid this it makes application management easier. Each application should also have its own index tablespace. This results in a simple formula for determining the number of tablespaces:

5+2 times the number of application expected

Some applications may require multiple tablespaces, such as in the case where for performance you want to separate out large tables from the rest of the application. In one case, a single application generated 13 tablespacees. Most applications aren't as complicated as this and will only require two tablespaces. The purists will claim each table should be in its own tablespace, but this is overkill in many cases.

Sizing of tablespaces is a difficult question. Each tablespace will have unique requirements. Here are some general guidelines:

The SYSTEM tablespace, if you split out the tool tables, should only require 40 to 90 MB of disk space.

The TOOLS tablespace will depend entirely on the amount of development you expect. At one site with 16 applications being developed, nearly 90 MB were required for the TOOLS tables.

The ROLLBACK tablespace will again be driven by the number and size of rollback segments you require. The number and size of rollback segments is driven by the number of transactions per rollback segment, the number of users, and the maximum size of nonbatch transactions. You can create a large rollback segment and leave it off-line until it is needed for a large transaction and then use the SET TRANSACTION USE ROLLBACK SEGMENT command to utilize it after bringing it on-line. the number of rollback segments is driven by the number or expected transactions and can be estimated by the equation:

NUMBER OF TRANSACTIONS / TRANSACTIONS PER ROLLBACK SEGMENT

The number of transactions will be driven by the number of user and types of database operations they will be doing. In fact, if the Oracle kernel sees a violation of the above formula, it will bring on-line any available public rollback segments.

The DEFAULT USER tablespace size will depend upon the number of users you want to assign to it and the estimated size of tables they will be using. In most cases, 10 to 20 MB is sufficient. If you expect heavy usage, assign quotas to each user.

The TEMPORARY USER tablespace should be up to twice the size of your largest table if you use RULE-based optimization and up to four times the size of your largest table for COST-based, and is also dependent on the number of users and the size of sorts or joins they perform. An improperly designed join between large tables can fill a temporary area fast. For example, an unrestricted outside join of two thousand row tables will result in a one-million-row temporary sort table. If those rows are each several hundred bytes long, there goes your temporary space. Unfortunately, there isn't much that can be done other than training developers or ad hoc query generators not to do unrestricted joins of large tables. If a temporary tablespace gets filled, the users who are assigned to it cannot perform operations requiring temporary space, or, worse, the temporary space may be taken from the SYSTEM area. There is a valid argument for having several temporary areas if you have a large number of users. In one instance, a 10-MB temporary tablespace was completely filled by a single multi-table outside join jusing DECODE statements.

If you have the disk space, placing the TEMPORARY USER tablespaces on a disk of its own will improve query and report performance due to reduction of disk contention, especially for large reports or queries.

Renaming Tablespace (10G)

In 10g tablespaces can be renamed as follows:

ALTER TABLESPACE aaaa RENAME TO bbbb;

When a tablespace is renamed, the database updates all references on this tablespace in the control file, datafile header and data dictionary. The SYSTEM and SYSAUX tablespaces cannot be renamed. All other tablespaces can be renamed provided all datafiles are online. If a tablespace is read only then the database will not update the datafile header and only the data dictionary and control file will be updated. In this case it will write a message in the alert log saying that it could not update the datafile header. If an undo tablespace is renamed and the same undo tablespace is given in the UNDO_TABLESPACE parameter the database will update the spfile also.