This chapter contains the following topics:
Oracle Database stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. Figure 3-1 illustrates this relationship.
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
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 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:
Redo log 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.
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.
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.
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.
See Also:Oracle Database 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. Extents are a collection of contiguous blocks.
This section includes the following topics about tablespaces:
Oracle Database lets you create bigfile tablespaces. This allows Oracle Database to contain tablespaces made up of single large files rather than numerous smaller ones. This lets Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. The consequence of this is that Oracle Database can now scale up to 8 exabytes in size.
With Oracle-managed files, bigfile tablespaces make datafiles completely transparent for users. In other words, you can perform operations on tablespaces, rather than the underlying datafile. Bigfile tablespaces make the tablespace the main unit of the disk space administration, backup and recovery, and so on. Bigfile tablespaces also simplify datafile management with Oracle-managed files and Automatic Storage Management by eliminating the need for adding new datafiles and dealing with multiple files.
The system default is to create a smallfile tablespace, which is the traditional type of Oracle Database tablespace. The
SYSAUX tablespace types are always created using the system default type.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management. There are two exceptions: locally managed undo and temporary tablespaces can be bigfile tablespaces, even though their segments are manually managed.
An Oracle database can contain both bigfile and smallfile tablespaces. Tablespaces of different types are indistinguishable in terms of execution of SQL statements that do not explicitly refer to datafiles.
You can create a group of temporary tablespaces that let a user consume temporary space from multiple tablespaces. A tablespace group can also be specified as the default temporary tablespace for the database. This is useful with bigfile tablespaces, where you could need a lot of temporary tablespace for sorts.
This section includes the following topics:
Bigfile tablespaces can significantly increase the storage capacity of an Oracle database. Smallfile tablespaces can contain up to 1024 files, but bigfile tablespaces contain only one file that can be 1024 times larger than a smallfile tablespace. The total tablespace capacity is the same for smallfile tablespaces and bigfile tablespaces. However, because there is limit of 64K datafiles for each database, a database can contain 1024 times more bigfile tablespaces than smallfile tablespaces, so bigfile tablespaces increase the total database capacity by 3 orders of magnitude. In other words, 8 exabytes is the maximum size of the Oracle database when bigfile tablespaces are used with the maximum block size (32 k).
Bigfile tablespaces simplify management of datafiles in ultra large databases by reducing the number of datafiles needed. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.
They simplify database management by providing datafile transparency.
Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.
Avoid using bigfile tablespaces if there could possibly be no free space available on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.
Performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of traditional tablespaces. However, increasing the datafile size might increase time to restore a corrupted file or create a new datafile.
See Also:Oracle Database Administrator's Guide for details on creating, altering, and administering bigfile tablespaces
To take advantage of the benefits of locally managed tablespaces, you can create a locally managed
SYSTEM tablespace, or you can migrate an existing dictionary managed
SYSTEM tablespace to a locally managed format.
In a database with a locally managed
SYSTEM tablespace, dictionary managed tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.
This section includes the following topics:
All data stored on behalf of stored PL/SQL program units (that is, procedures, functions, packages, and triggers) resides in the
SYSTEM tablespace. If the database contains many of these program units, then the database administrator must provide the space the units need in the
Oracle Database Administrator's Guide for information about creating or migrating to a locally managed
"Online and Offline Tablespaces" for information about the permanent online condition of the
SYSAUX tablespace is an auxiliary tablespace to the
SYSTEM tablespace. Many database components use the
SYSAUX tablespace as their default location to store data. Therefore, the
SYSAUX tablespace is always created during database creation or database upgrade.
SYSAUXtablespace is unavailable, such as due to a media failure, then some database features may fail.
SYSAUX tablespace provides a centralized location for database metadata that does not reside in the
SYSTEM tablespace. It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases.
During normal database operation, Oracle Database does not allow the
SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for
SYSAUX is not supported.
See Also:Oracle Database Administrator's Guide to learn about database components that use the
Undo tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Undo tablespaces are used only when the database is in automatic undo management mode (the default). A database can contain more than one undo tablespace, but only one can be in use at any time. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by the database.
When the first DML operation is run within a transaction, the transaction is bound (assigned) to an undo segment (and therefore to a transaction table) in the current undo tablespace. In rare circumstances, if the instance does not have a designated undo tablespace, the transaction binds to the system undo segment.
Each undo tablespace is composed of a set of datafiles and is locally managed. Like other types of tablespaces, undo blocks are grouped in extents and the status of each extent is represented in the bitmap. At any point in time, an extent is either allocated to (and used by) a transaction table, or it is free.
You can create a bigfile undo tablespace.
An undo tablespace is automatically created with each new installation of Oracle Database. Earlier versions of Oracle Database may not include an undo tablespace and may instead use rollback segments. This is known as manual undo management mode. When upgrading to Oracle Database 11g you can migrate to automatic undo management by creating an undo tablespace and enabling automatic undo management mode. See Oracle Database Upgrade Guide for details.
SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed
SYSTEM tablespace cannot be used for default temporary storage.
SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then
SYSTEM is still used for default temporary storage. However, you will receive a warning in
LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.
Specify default temporary tablespaces when you create a database, using the
DEFAULT TEMPORARY TABLESPACE extension to the
CREATE DATABASE statement.
You can create bigfile temporary tablespaces. A bigfile temporary tablespace, like all temporary tablespaces, uses tempfiles instead of datafiles.
Note:You cannot make a default temporary tablespace permanent or take it offline.
A very small database may need only the
SYSTEM tablespace; however, Oracle 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 perform the following tasks:
Control disk space allocation for database data
Assign specific space quotas for database users
Control availability of data by taking individual tablespaces online or offline
Perform partial database backup or recovery operations
Allocate data storage across devices to improve performance
A database administrator can perform the following actions:
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
Transport tablespaces across databases and platforms
Locally managed tablespaces: Extent management by the bitmaps
Dictionary managed tablespaces: Extent management by the data dictionary
When you create a tablespace, you choose one of these methods of space management. Later, you can change the management method with the
DBMS_SPACE_ADMIN PL/SQL package.
This section includes the following topics:
See Also:"Overview of Extents"
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 Database changes the bitmap values to show the new status of the blocks.
Locally managed tablespaces have the following advantages over dictionary managed tablespaces:
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
Local management of extents avoids recursive space management operations. Such recursive operations can occur in dictionary managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a data dictionary table or rollback segment.
The sizes of extents that are managed locally are determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options.
LOCAL clause of the
CREATE TABLESPACE or
CREATE TEMPORARY TABLESPACE statement is specified to create locally managed permanent or temporary tablespaces, respectively.
When you create a locally managed tablespace using the
CREATE TABLESPACE statement, the
SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed. Your choices are:
This keyword tells Oracle Database that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps enable Oracle Database to manage free space more automatically; thus, this form of space management is called automatic segment-space management.
Locally managed tablespaces using automatic segment-space management can be created as smallfile (traditional) or bigfile tablespaces.
AUTO is the default.
This keyword tells Oracle Database that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows.
If you created your database with Oracle9i, you could be using dictionary managed tablespaces. For a tablespace that uses the data dictionary to manage its extents, Oracle Database updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. Oracle Database also stores rollback information about each update of the dictionary tables. 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.
Note:If you do not specify extent management when you create a tablespace, then the default is locally managed.
Oracle Database supports multiple block sizes in a database. The standard block size is used for the
SYSTEM tablespace. This is set when the database is created and can be any valid size. You specify the standard block size by setting the initialization parameter
DB_BLOCK_SIZE. Legitimate values are from 2K to 32K.
In the initialization parameter file or server parameter file, you can configure subcaches within the buffer cache for each of these block sizes. Subcaches can also be configured while an instance is running. You can create tablespaces having any of these block sizes. The standard block size is used for the system tablespace and most other tablespaces.
Note:All partitions of a partitioned object must reside in tablespaces of a single block size.
Multiple block sizes are useful primarily when transporting a tablespace from an OLTP database to an enterprise data warehouse. This facilitates transport between databases of different block sizes.
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 Database.
A tablespace is usually online so that the data contained within it is available to database users. However, the database administrator can take a tablespace offline for maintenance or backup and recovery purposes.
When a tablespace goes offline, Oracle Database 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 Database 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 Database 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 is 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 Database. Thus, offline tablespaces cannot be transposed to other databases.
Oracle Database automatically switches a tablespace from online to offline when certain errors are encountered. For example, Oracle Database switches a tablespace from online to offline 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 problem.
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 Database 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 requirements.
Read-only tablespaces cannot be modified. To update a read-only tablespace, 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, and as long as they have not been made read/write at any point, they do not need repeated backup. Also, if you must recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.
You can manage space for sort operations more efficiently by designating one or more temporary tablespaces exclusively for sorts. Doing so effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space. A single SQL operation can use more than one temporary tablespace for sorting. For example, you can create indexes on very large tables, and the sort operation during index creation can be distributed across multiple tablespaces.
All operations that use sorts, including joins, index builds, ordering, computing aggregates (
BY), and collecting optimizer statistics, benefit from temporary tablespaces. The performance gains are significant with Oracle Real Application Clusters.
This section includes the following topics:
One or more temporary tablespaces can be used only for sort 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.
See Also:Chapter 2, "Data Blocks, Extents, and Segments" for more information about segments
Create temporary tablespaces by using the
CREATE TABLESPACE or
CREATE TEMPORARY TABLESPACE statement.
"Temporary Datafiles" for information about
"Managing Space in Tablespaces" for information about locally managed and dictionary managed tablespaces
Oracle Database SQL Language Reference for syntax
Oracle Database Performance Tuning Guide for information about setting up temporary tablespaces for sorts and hash joins
A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another, even across different platforms. You can clone a 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 you do not have to rebuild the indexes after importing or loading the table data.
You can transport tablespaces across platforms. (Many, but not all, platforms are supported for cross-platform tablespace transport.) This can be used for the following:
Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on a different platform
Simplify the distribution of data from a data warehouse environment to data marts which are often running on smaller platforms
Enable the sharing of read only tablespaces across a heterogeneous cluster
Allow a database to be migrated from one platform to another
This section includes the following topics:
A tablespace repository is a collection of tablespace sets. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases. Different tablespace sets may be stored in a tablespace repository, and different versions of a particular tablespace set also may be stored. A version of a tablespace set in a tablespace repository consists of the following files:
The Data Pump export dump file for the tablespace set
The Data Pump log file for the export
The datafiles that comprise the tablespace set
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 the 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.
The first time a tablespace's datafiles are opened under Oracle Database with the
COMPATIBLE initialization parameter set to 10 or higher, each file identifies the platform to which it belongs. These files have identical on disk formats for file header blocks, which are used for file identification and verification. Read only and offline files get the compatibility advanced after they are made read/write or are brought online. This implies that tablespaces that are read only before Oracle Database 10g must be made read/write at least once before they can use the cross platform transportable feature.
Note:In a database with a locally managed
SYSTEMtablespace, dictionary tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.
Oracle Database Administrator's Guide for details about how to move or copy tablespaces to another database, including details about transporting tablespaces across platforms
Oracle Database Utilities for import/export information
Oracle Database PL/SQL Packages and Types Reference for information on the
Oracle Streams Concepts and Administration for more information on ways to copy or transport files
Oracle Database 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 under which Oracle Database runs is responsible for clearing old information and authorizations from a file before allocating it to Oracle Database. If the file is large, this process can take a significant amount of time. The first tablespace in any database is always the
SYSTEM tablespace, so Oracle Database automatically allocates the first datafiles of any database for the
SYSTEM tablespace during database creation.
This section includes the following topics:
See Also:Your Oracle Database operating system-specific documentation for information about the amount of space required for the file header of datafiles on your operating system
When a datafile is first created, the allocated disk space is formatted but does not contain any user data. However, Oracle Database reserves the space to hold the data for future segments of the associated tablespace—it is used exclusively by Oracle Database. As the data grows in a tablespace, Oracle Database uses the free space in the associated datafiles to allocate extents for the segment.
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 Database 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.
See Also:Chapter 2, "Data Blocks, Extents, and Segments" for more information about use of space
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 for each tablespace and can simplify administration of datafiles.
Note:You need sufficient space on the operating system for expansion.
See Also:Oracle Database Administrator's Guide for more information about resizing datafiles
You can take tablespaces offline or bring them online at any time, except for the
SYSTEM tablespace. All of the datafiles of 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 usually done only during some database recovery procedures.
Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles, with the following exceptions:
Tempfiles are always set to
You cannot make a tempfile read only.
You cannot create a tempfile with the
Media recovery does not recognize tempfiles:
BACKUP CONTROLFILE does not generate any information for tempfiles.
CREATE CONTROLFILE cannot specify any information about tempfiles.
When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.
Caution:This enables fast tempfile creation and resizing; however, the disk could run out of space later when the tempfiles are accessed.
Tempfile information is shown in the dictionary view
DBA_TEMP_FILES and the dynamic performance view
V$TEMPFILE, but not in
DBA_DATA_FILES or the
See Also:"Managing Space in Tablespaces" for more information about locally managed tablespaces
The database control file is a small binary file necessary for the database to start and operate successfully. A control file is updated continuously by Oracle Database during database use, so it must be available for writing whenever the database is open. If for some reason the control file is not accessible, then the database cannot function properly.
Each control file is associated with only one Oracle database.
This section includes the following topics:
A control file contains information about the associated database that is required for access by an instance, both at startup and during normal operation. Control file information can be modified only by Oracle Database; no database administrator or user can edit a control file.
Among other things, a control file contains information such as:
The timestamp of database creation
Datafile offline ranges
The log history
Archived log information
Backup set and backup piece information
Backup datafile and redo log information
Datafile copy information
The database name and timestamp originate at database creation. The database name is taken from either the name specified by the
DB_NAME initialization parameter or the name used in the
Each time that a datafile or a redo log file is added to, renamed in, or dropped from the database, the control file is updated to reflect this physical structure change. These changes are recorded so that:
Oracle Database can identify the datafiles and redo log files to open during database startup
Oracle Database can identify files that are required or available in case database recovery is necessary
Therefore, if you make a change to the physical structure of your database (using
DATABASE statements), then you should immediately make a backup of your control file.
Control files also record information about checkpoints. Every three seconds, the checkpoint process (CKPT) records information in the control file about the checkpoint position in the redo log. This information is used during database recovery to tell Oracle Database that all redo entries recorded before this point in the redo log group are not necessary for database recovery; they were already written to the datafiles.
See Also:Oracle Database Backup and Recovery User's Guide for information about backing up a database's control file
As with redo log files, Oracle Database enables multiple, identical control files to be open concurrently and written for the same database. By storing multiple control files for a single database on different disks, you can safeguard against a single point of failure with respect to control files. If a single disk that contained a control file crashes, then the current instance fails when Oracle Database attempts to access the damaged control file. However, when other copies of the current control file are available on different disks, an instance can be restarted without the need for database recovery.
If all control files of a database are permanently lost during operation, then the instance is aborted and media recovery is required. Media recovery is not straightforward if an older backup of a control file must be used because a current copy is not available. It is strongly recommended that you adhere to the following:
Use multiplexed control files with each database
Store each copy on a different physical disk
Use operating system mirroring