ORACLE TABLESPACES

Creating Tablespaces

Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTEM tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM tablespace or take it offline.

The SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the SYSTEM tablespace, management of the SYSAUX tablespace requires a higher level of security and you cannot rename or drop it. The management of the SYSAUX tablespace is discussed separately in "Managing the SYSAUX Tablespace".

3 Tablespaces, Datafiles, and Control Files

This chapter describes tablespaces, the primary logical database structures of any Oracle database, and the physical datafiles that correspond to each tablespace.

This chapter contains the following topics:

• Introduction to Tablespaces, Datafiles, and Control Files
• Overview of Tablespaces
• Overview of Datafiles
• Overview of Control Files

Introduction to Tablespaces, Datafiles, and Control Files

Oracle Database stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. Figure 3-1 illustrates this relationship.

Figure 3-1 Datafiles and Tablespaces

Description of "Figure 3-1 Datafiles and Tablespaces"

Databases, tablespaces, and datafiles are closely related, but they have important differences:

• An Oracle database consists of at least two logical storage units called tablespaces, which collectively store all of the database's data. You must have the SYSTEM and SYSAUX tablespaces and a third tablespace, called TEMP, is optional.
• Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle Database is running.
• A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

This section includes the following topics:

• Oracle-Managed Files
• Allocate More Space for a Database

Oracle-Managed Files

Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames. Oracle Database internally uses standard file system interfaces to create and delete files as needed for the following database structures:

• Tablespaces
• Redo log files
• Control files

Through initialization parameters, you specify the file system directory to be used for a particular type of file. Oracle Database then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.

See Also:

• Oracle Database Administrator's Guide
• "Automatic Storage Management"

Allocate More Space for a Database

The size of a tablespace is the size of the datafiles that constitute the tablespace. The size of a database is the collective size of the tablespaces that constitute the database.
You can enlarge a database in three ways:

• Add a datafile to a tablespace
• Add a new tablespace
• Increase the size of a datafile

When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace. Figure 3-2 illustrates this kind of space increase.

Figure 3-2 Enlarging a Database by Adding a Datafile to a Tablespace

Description of "Figure 3-2 Enlarging a Database by Adding a Datafile to a Tablespace"

Alternatively, you can create a new tablespace (which contains at least one additional datafile) to increase the size of a database. Figure 3-3 illustrates this.

Figure 3-3 Enlarging a Database by Adding a New Tablespace

Description of "Figure 3-3 Enlarging a Database by Adding a New Tablespace"

The third option for enlarging a database is to change a datafile's size or let datafiles in existing tablespaces grow dynamically as more space is needed. You accomplish this by altering existing files or by adding files with dynamic extension properties. Figure 3-4 illustrates this.

Figure 3-4 Enlarging a Database by Dynamically Sizing Datafiles

Description of "Figure 3-4 Enlarging a Database by Dynamically Sizing Datafiles"

References

1. http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm

2. http://docs.oracle.com/cd/B28359_01/server.111/b28318/physical.htm

3. http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7003.htm
4. http://www.siue.edu/~dbock/cmis565/module4-tablespaces_and_datafiles.htm