Oracle7 Server Concepts Manual Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

Tablespaces and Datafiles

Space -- the final frontier...

Gene Roddenberry: Star Trek

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

If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for more information about tablespaces and datafiles in that environment.

An Introduction to Tablespaces and Datafiles

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

Figure 4 - 1. Datafiles and Tablespaces

Although databases, tablespaces, datafiles, and segments are closely related, they have important differences:

databases and tablespaces

An Oracle database is comprised of one or more logical storage units called tablespaces. The database's data is collectively stored in the database's tablespaces.

tablespaces and datafiles

Each tablespace in an Oracle database is comprised of one or more operating system files called datafiles. A tablespace's datafiles physically store the associated database data on disk.

databases and datafiles

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. A more complicated database might have three tablespaces, each comprised of two datafiles (for a total of six datafiles).

schema objects, segments, and tablespaces

When a schema object such as a table or index is created, its segment is created within a designated tablespace in the database. For example, suppose you create a table in a specific tablespace using the CREATE TABLE command with the TABLESPACE option. Oracle allocates the space for this table's data segment in one or more of the datafiles that constitute the specified tablespace. An object's segment allocates space in only one tablespace of a database. See Chapter 3, "Data Blocks, Extents, and Segments", for more information about extents and segments and how they relate to tablespaces.

The following sections further explain tablespaces and datafiles.


A database is divided into one or more logical storage units called tablespaces. A database administrator can use tablespaces to do the following:

A database administrator can create new tablespaces, add and remove datafiles from tablespaces, set and alter default segment storage settings for segments created in a tablespace, make a tablespace read-only or writeable, make a tablespace temporary or permanent, and drop tablespaces.

This section includes the following topics:

The SYSTEM Tablespace

Every Oracle database contains a tablespace named SYSTEM that Oracle creates automatically when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

A small database might need only the SYSTEM tablespace; however, it is recommended that you create at least one additional tablespace to store user data separate from data dictionary information. This allows you more flexibility in various database administration operations and can reduce contention among dictionary objects and schema objects for the same datafiles.

Note: The SYSTEM tablespace must always be kept online. See "Online and Offline Tablespaces" [*].

All data stored on behalf of stored PL/SQL program units (procedures, functions, packages and triggers) resides in the SYSTEM tablespace. If you create many of these PL/SQL objects, the database administrator needs to plan for the space in the SYSTEM tablespace that these objects use. For more information about these objects and the space that they require, see Chapter 14, "Procedures and Packages", and Chapter 15, "Database Triggers".

Allocating More Space for a Database

To enlarge a database, you have three options. You can add another datafile to one of its existing tablespaces, thereby increasing the amount of disk space allocated for the corresponding tablespace. Figure 4 - 2 illustrates this kind of space increase.

Figure 4 - 2. Enlarging a Database by Adding a Datafile to a Tablespace

Alternatively, a database administrator can create a new tablespace (defined by an additional datafile) to increase the size of a database. Figure 4 - 3 illustrates this.

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

The size of a tablespace is the size of the datafile(s) that constitute the tablespace, and the size of a database is the collective size of the tablespaces that constitute the database.

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

Figure 4 - 4. Enlarging a Database by Dynamically Sizing Datafiles

For more information about increasing the amount of space in your database, see the Oracle7 Server Administrator's Guide.

Online and Offline Tablespaces

A database administrator can bring any tablespace (except the SYSTEM tablespace) in an Oracle database online (accessible) or offline (not accessible) whenever the database is open.

Note: The SYSTEM tablespace must always be online because the data dictionary must always be available to Oracle.

A tablespace is normally online so that the data contained within it is available to database users. However, the database administrator might take a tablespace offline for any of the following reasons:

When a Tablespace Goes Offline

When a tablespace goes offline, Oracle does not permit any subsequent SQL statements to reference objects contained in the tablespace. Active transactions with completed statements that refer to data in a tablespace that has been taken offline are not affected at the transaction level. Oracle saves rollback data corresponding to statements that affect data in the offline tablespace in a deferred rollback segment (in the SYSTEM tablespace). When the tablespace is brought back online, Oracle applies the rollback data to the tablespace, if needed.

You cannot take a tablespace offline if it contains any rollback segments that are in use.

When a tablespace goes offline or comes back online, it is recorded in the data dictionary in the SYSTEM tablespace. If a tablespace was offline when you shut down a database, the tablespace remains offline when the database is subsequently mounted and reopened.

You can bring a tablespace online only in the database in which it was created because the necessary data dictionary information is maintained in the SYSTEM tablespace of that database. An offline tablespace cannot be read or edited by any utility other than Oracle. Thus, tablespaces cannot be transferred from database to database (transfer of Oracle data can be achieved with tools described in Oracle7 Server Utilities).

Oracle automatically changes a tablespace from online to offline when certain errors are encountered (for example, when the database writer process, DBWR, fails in several attempts to write to a datafile of the tablespace). Users trying to access tables in the tablespace with the problem receive an error. If the problem that causes this disk I/O to fail is media failure, the tablespace must be recovered after you correct the hardware problem.

Using Tablespaces for Special Procedures

By using multiple tablespaces to separate different types of data, the database administrator can also take specific tablespaces offline for certain procedures, while other tablespaces remain online and the information in them is still available for use. However, special circumstances can occur when tablespaces are taken offline. For example, if two tablespaces are used to separate table data from index data, the following is true:

In summary, if Oracle determines that it has enough information in the online tablespaces to execute a statement, it will do so. If it needs data in an offline tablespace, then it causes the statement to fail.

Read-Only Tablespaces

The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Oracle never updates the files of a read-only tablespace, and therefore the files can reside on read-only media, such as CD ROMs or WORM drives.

Note: Because you can only bring a tablespace online in the database in which it was created, read-only tablespaces are not meant to satisfy archiving or data publishing requirements.

Whenever you create a new tablespace, it is always created as read-write. The READ ONLY option of the ALTER TABLESPACE command allows you to change the tablespace to read-only, making all of its associated datafiles read-only as well. You can then use the READ WRITE option to make a read-only tablespace writeable again.

Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.

You can drop items, such as tables and indexes, from a read-only tablespace, just as you can drop items from an offline tablespace. However, you cannot create or alter objects in a read-only tablespace.

Making a Tablespace Read-Only

Use the SQL command ALTER TABLESPACE to change a tablespace to read-only. For information on the ALTER TABLESPACE command, see the Oracle7 Server SQL Reference.

Read-Only vs. Online or Offline

Making a tablespace read-only does not change its offline or online status.

Offline datafiles cannot be accessed. Bringing a datafile in a read-only tablespace online makes the file readable. The file cannot be written to unless its associated tablespace is returned to the read-write state. The files of a read-only tablespace can independently be taken online or offline using the DATAFILE option of the ALTER DATABASE command.

Restrictions on Read-Only Tablespaces

You cannot add datafiles to a tablespace that is read-only, even if you take the tablespace offline. When you add a datafile, Oracle must update the file header, and this write operation is not allowed.

To update a read-only tablespace, you must first make the tablespace writeable. After updating the tablespace, you can then reset it to be read-only.

Read-Only Tablespaces and Recovery

Read-only tablespaces have several implications upon instance or media recovery. See Chapter 24, "Database Recovery", for more information about recovery.

Temporary Tablespaces

Space management for sort operations is performed more efficiently using temporary tablespaces designated exclusively for sorts. This scheme effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space. All operations that use sorts, including joins, index builds, ordering (ORDER BY), the computation of aggregates (GROUP BY), and the ANALYZE command to collect optimizer statistics, benefit from temporary tablespaces. The performance gains are significant in parallel server environments.

A temporary tablespace is a tablespace that can only be used for sort segments. No permanent objects can reside in a temporary tablespace. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists in every instance that performs a sort operation in a given tablespace.

Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation. The sort segment grows by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance.

You create temporary tablespaces using the following SQL syntax:


You can also alter a tablespace from PERMANENT to TEMPORARY or vice versa using the following syntax:


For more information on the CREATE TABLESPACE and ALTER TABLESPACE Commands, see Chapter 4 of Oracle7 Server SQL Reference.


A tablespace in an Oracle database consists of one or more physical datafiles. A datafile can be associated with only one tablespace, and only one database.

When a datafile is created for a tablespace, Oracle creates the file by allocating the specified amount of disk space plus the overhead required for the file header. When a datafile is created, the operating system is responsible for clearing old information and authorizations from a file before allocating it to Oracle. If the file is large, this process might take a significant amount of time.

Additional Information: For information on the amount of space required for the file header of datafiles on your operating system, see your Oracle operating system specific documentation.

Since the first tablespace in any database is always the SYSTEM tablespace, Oracle automatically allocates the first datafiles of any database for the SYSTEM tablespace during database creation.

Datafile Contents

After a datafile is initially created, the allocated disk space does not contain any data; however, Oracle reserves the space to hold only the data for future segments of the associated tablespace -- it cannot store any other program's data. As a segment (such as the data segment for a table) is created and grows in a tablespace, Oracle uses the free space in the associated datafiles to allocate extents for the segment.

The data in the segments of objects (data segments, index segments, rollback segments, and so on) in a tablespace are physically stored in one or more of the datafiles that constitute the tablespace. Note that a schema object does not correspond to a specific datafile; rather, a datafile is a repository for the data of any object within a specific tablespace. Oracle allocates the extents of a single segment in one or more datafiles of a tablespace; therefore, an object can "span" one or more datafiles. Unless table "striping" is used, the database administrator and end-users cannot control which datafile stores an object.

Size of Datafiles

You can alter the size of a datafile after its creation or you can specify that a datafile should dynamically grow as objects in the tablespace grow. This functionality allows you to have fewer datafiles per tablespace and can simplify administration of datafiles.

For more information about resizing datafiles, see the Oracle7 Server Administrator's Guide.

Offline Datafiles

You can take tablespaces offline (make unavailable) or bring them online (make available) at any time. Therefore, all datafiles making up a tablespace are taken offline or brought online as a unit when you take the tablespace offline or bring it online, respectively. You can take individual datafiles offline; however, this is normally done only during certain database recovery procedures.

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index