Oracle8i Concepts
Release 8.1.5






Prev Next

Tablespaces and Datafiles

Space--the final frontier . . .

Gene Roddenberry: Star Trek

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

Databases, Tablespaces, and Datafiles

Oracle 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

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

databases and tablespaces  

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data.  

tablespaces and datafiles  

Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform with the operating system in which Oracle is running.  

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

Allocating More Space for a Database

You can enlarge a database in three ways:

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

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

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

The third option for enlarging a database 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 3-4 illustrates this.

Figure 3-4 Enlarging a Database by Dynamically Sizing Datafiles

Additional Information:

See the Oracle8i Administrator's Guide for more information about increasing the amount of space in your database.  


A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents (see Chapter 4, "Data Blocks, Extents, and Segments").

This section includes the following topics about tablespaces:

The SYSTEM Tablespace

Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created.


The SYSTEM tablespace is always online when the database is open. See "Online and Offline Tablespaces".  

The Data Dictionary

The SYSTEM tablespace always contains the data dictionary tables for the entire database. The data dictionary tables are stored in datafile 1.

PL/SQL Program Units

All data stored on behalf of stored PL/SQL program units (procedures, functions, packages, and triggers) resides in the SYSTEM tablespace. If the database will contain many of these program units, the database administrator needs to allow for the space they use in the SYSTEM tablespace.

For more information about PL/SQL program units and the space that they require, see Chapter 18, "Procedures and Packages", and Chapter 20, "Triggers".

Using Multiple Tablespaces

A small database might need only the SYSTEM tablespace; however, Oracle Corporation recommends that you create at least one additional tablespace to store user data separate from data dictionary information. This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same datafiles.

You can use multiple tablespaces to:

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

Space Management in Tablespaces

Tablespaces allocate space in extents (see "Extents"). Tablespaces can use two different methods to keep track of their free and used space:

When you create a tablespace, you choose one of these methods of space management. You cannot alter the method at a later time.

Dictionary-Managed Tablespaces

For a tablespace that uses the data dictionary to manage its extents, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. Oracle also stores rollback information about each update of the dictionary tables. (See "Rollback Segments".) Because dictionary tables and rollback segments are part of the database, the space that they occupy is subject to the same space management operations as all other data.

This is the default method of space management in a tablespace. It was the only method available in Oracle releases 8.0 and earlier.

Locally-Managed Tablespaces

A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

Locally-managed tablespaces have the following advantages over dictionary-managed tablespaces:

The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally-managed tablespace. See "Extents Managed Locally" for more information.

The LOCAL option of the EXTENT MANAGEMENT clause specifies this method of space management in various CREATE commands:

Online and Offline Tablespaces

A database administrator can bring any tablespace other than the SYSTEM tablespace online (accessible) or offline (not accessible) whenever the database is open. The SYSTEM tablespace is always online when the database is open 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

You cannot take a tablespace offline if it contains any rollback segments that are in use. See "Rollback Segments" for more information.

When a Tablespace Goes Offline

When a tablespace goes offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tablespace. Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level. Oracle saves rollback data corresponding to those completed statements 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.

When a tablespace goes offline or comes back online, this 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, offline tablespaces cannot be transferred from database to database. (See "Temporary Tablespaces" for a way to transfer online tablespaces between databases.)

Additional Information:

Transfer of Oracle data between databases can be achieved with tools described in Oracle8i Utilities.  

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

Using Tablespaces for Special Procedures

If you create multiple tablespaces to separate different types of data, you take specific tablespaces offline for various procedures; 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 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.


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. You can change the tablespace to read-only with the READ ONLY option of the ALTER TABLESPACE command, making all of the tablespace's associated datafiles read-only as well.

The ALTER TABLESPACE ... READ ONLY command places the tablespace in a transitional read-only mode and waits for existing transactions to complete (commit or roll back). This transitional state does not allow any further write operations to the tablespace except for the rollback of existing transactions that previously modified blocks in the tablespace. Hence, in transition the tablespace behaves like a read-only tablespace for all user commands except ROLLBACK. After all of the existing transactions have either committed or rolled back, the ALTER TABLESPACE ... READ ONLY command completes and the tablespace is placed in read-only mode.


The transitional read-only state only occurs if the value of the initialization parameter COMPATIBLE is 8.1.0 or greater. For parameter values less than 8.1.0, the ALTER TABLESPACE ... READ ONLY command will fail if any active transactions exist.  

You can use the READ WRITE option of the ALTER TABLESPACE command to make a read-only tablespace read-write again.

Additional Information:

See the Oracle8i Administrator's Guide for more information on changing a tablespace to read-only or read-write mode, and see the Oracle8i SQL Reference for information on the ALTER TABLESPACE command.  

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 only readable. The file cannot be written to unless its associated tablespace is returned to the read-write state. You can take the files of a read-only tablespace online or offline independently using the DATAFILE option of the ALTER DATABASE command.

Read-only tablespaces cannot be modified. To update a read-only tablespace, you must first make the tablespace read-write. After updating the tablespace, you can then reset it to be read-only.

Because read-only tablespaces cannot be modified, 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. However, read-only tablespaces may need attention during instance or media recovery, depending upon whether and when they have ever been read-write.

Additional Information:

See the Oracle8i Backup and Recovery Guide for more information about recovery.  

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.

You cannot add datafiles to a read-only tablespace, 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 in a read-only tablespace.

Temporary Tablespaces

You can manage space for sort operations more efficiently by designating temporary tablespaces exclusively for sorts. Doing so 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 for collecting optimizer statistics--benefit from temporary tablespaces. The performance gains are significant in Oracle Parallel Server environments.

Sort Segments

A temporary tablespace can be used only for sort segments. (See Chapter 4, "Data Blocks, Extents, and Segments" for information about segments.) A temporary tablespace is not the same as a tablespace that a user designates for temporary segments, which can be any tablespace available to the user. No permanent schema objects can reside in a temporary tablespace.

Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists for 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 expands 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.

Creating and Altering Temporary Tablespaces

You create temporary tablespaces by using the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE command:

You can also change a tablespace from PERMANENT to TEMPORARY or vice versa by using the ALTER TABLESPACE command for any temporary tablespace (either locally managed or dictionary-managed). See "Space Management in Tablespaces" for information about locally managed and dictionary-managed tablespaces.

Additional Information:

See Oracle8i SQL Reference for more information on the CREATE TABLESPACE, CREATE TEMPORARY TABLESPACE, and ALTER TABLESPACE commands, and see Oracle8i Tuning for information about how to set up temporary tablespaces for sorts and hash joins.  

Transporting Tablespaces between Databases

The transportable tablespace feature enables you to move a subset of an Oracle database from one Oracle database to another. You can clone a tablespace from one tablespace and plug it into another database, copying the tablespace between databases, or you can unplug a tablespace from one Oracle database and plug it into another Oracle database, moving the tablespace between databases.

Moving data by transporting tablespaces can be orders of magnitude faster than either export/import or unload/load of the same data, because transporting a tablespace involves only copying datafiles and integrating the tablespace metadata. When you transport tablespaces you can also move index data, so that you do not have to rebuild the indexes after importing or loading the table data.

In the current release, you can transport tablespaces only between Oracle databases that use the same data block size and character set, and that run on compatible platforms from the same hardware vendor.

Moving or Copying a Tablespace to Another Database

To move or copy a set of tablespaces, you must make the tablespaces read-only, copy the datafiles of these tablespaces, and use export/import to move the database information (metadata) stored in data dictionary. Both the datafiles and the metadata export file must be copied to the target database. The transport of these files can be done using any facility for copying flat files, such as the operating system copying facility, ftp, or publishing on CDs.

After copying the datafiles and importing the metadata, you can optionally put the tablespaces in read-write mode.

Additional Information:

See Oracle8i Administrator's Guide for details about how to move or copy tablespaces to another database.  

Transportable Data Sets

You can transport a data set consisting of one or more tablespaces, as long as the set of schema objects in the data set is self-contained (except for object references--see "REFs"). If you transport a data set that contains a pointer to a BFILE, you must also move the BFILE and set the directory correctly in the target database.

If the data set includes a partitioned table, it must contain all of the table's partitions. To transport a subset of a partitioned table, you can exchange the partitions into tables before transporting them.

Tablespace Metadata

The metadata that you export can include or omit information about triggers, grants, and constraints, depending on which export options you use. Primary key constraints are always exported.

Benefits of Transporting Tablespaces

Transporting tablespaces is particularly useful for:

You can also transport tablespaces to move or copy data between Oracle databases that have different compatibility or release levels.

Additional Information:

See Oracle8i Migration for details about how to move or copy tablespaces between Oracle releases or compatibility levels.  

Data Warehouses and Data Marts

An enterprise data warehouse contains historical detailed data about the company. Typically, data flows from one or more online transaction processing (OLTP) databases into the data warehouse on a monthly, weekly, or daily basis. The data is usually processed in a staging database before being added to the data warehouse.

A data mart contains a subset of corporate data that is of value to a specific business unit, department, or set of users. Typically, a data mart is derived from an enterprise data warehouse.

Transporting tablespaces can be useful for many purposes in a data warehouse environment:

Data Publication

Content providers acquire data and make it available in a useful format. For example, a content provider might acquire statistical data from hospitals and provide it to insurance companies, or a telephone company might give large customers their billing data on CDs. Content providers can transport tablespaces to publish structured data on CD or other media, enabling customers to integrate the published data into their Oracle databases.


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.

Oracle creates a datafile for a tablespace by allocating the specified amount of disk space plus the overhead required for the file header. When a datafile is created, the operating system in which Oracle is running 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.  

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

Datafile Contents

When a datafile is first created, the allocated disk space is formatted but does not contain any user data; however, Oracle reserves the space to hold the data for future segments of the associated tablespace--it is used exclusively by Oracle. As the data grows in a tablespace, Oracle uses the free space in the associated datafiles to allocate extents for the segment. See Chapter 4, "Data Blocks, Extents, and Segments", for more information.

The data associated with schema objects in a tablespace is 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 schema object within a specific tablespace. Oracle allocates space for the data associated with a schema object in one or more datafiles of a tablespace. Therefore, a schema object can "span" one or more datafiles. Unless table "striping" is used (where data is spread across more than one disk), the database administrator and end users cannot control which datafile stores a schema 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 schema objects in the tablespace grow. This functionality enables you to have fewer datafiles per tablespace and can simplify administration of datafiles.

Additional Information:

See the Oracle8i Administrator's Guide for more information about resizing datafiles.  

Offline Datafiles

You can take tablespaces offline (make unavailable) or bring them online (make available) at any time, except for the SYSTEM tablespace. All of the 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 some database recovery procedures.

Temporary Datafiles

Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that:

See "Space Management in Tablespaces" for more information about locally managed tablespaces.


Copyright © 1999 Oracle Corporation.

All Rights Reserved.