Tablespaces

        A tablespace is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a tablespace stores data in one or more data files or temp files.

        A database must have the SYSTEM and SYSAUX tablespaces. Figure 12–27 shows the tablespaces in a typical database. The following sections describe the tablespace types.

Permanent Tablespaces

        A permanent tablespace groups persistent schema objects. The segments for objects in the tablespace are stored physically in data files.
        Each database user is assigned a default permanent tablespace. A very small database may need only the default SYSTEM and SYSAUX tablespaces. However, Oracle recommends that you create at least one tablespace to store user and application data. You can use tablespaces to achieve the following goals:
        ■Control disk space allocation for database data
        ■Assign a quota (space allowance or limit) to a database user
        ■Take individual tablespaces online or offline without affecting the availability of the whole database
        ■Perform backup and recovery of individual tablespaces
        ■Import or export application data by using the Oracle Data Pump utility (see "Oracle Data Pump Export and Import" on page 18-7)
        ■Create a transportable tablespace that you can copy or move from one database to another, even across platforms

        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 data files and integrating the tablespace metadata. When you transport tablespaces you can also move index data.

The SYSTEM Tablespace
        The SYSTEM tablespace is a necessary administrative tablespace included with the database when it is created. Oracle Database uses SYSTEM to manage the database.
        The SYSTEM tablespace includes the following information, all owned by the SYS user:
        ■The data dictionary
        ■Tables and views that contain administrative information about the database
        ■Compiled stored objects such as triggers, procedures, and packages
        The SYSTEM tablespace 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.
        By default, Oracle Database sets all newly created user tablespaces to be locally managed. In a database with a locally managed SYSTEM tablespace, you cannot create dictionary-managed tablespaces (which are deprecated). However, if you execute the CREATE DATABASE statement manually and accept the defaults, then the SYSTEM tables

pace is dictionary managed. You can migrate an existing dictionary-managed SYSTEM tablespace to a locally managed format.

        Note:
                Oracle strongly recommends that you use Database Configuration Assistant (DBCA) to create new databases so that all tablespaces, including SYSTEM, are locally managed by default

The SYSAUX Tablespace
        The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. The 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.
        Several database components, including Oracle Enterprise Manager and Oracle Streams, use the SYSAUX tablespace as their default storage location. Therefore, the SYSAUX tablespace is created automatically during database creation or upgrade.
During normal database operation, the database does not allow the SYSAUX tablespace to be dropped or renamed. If the SYSAUX tablespace becomes unavailable, then core database functionality remains operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

Undo Tablespaces
        An undo tablespace is a locally managed tablespace reserved for system-managed undo data (see "Undo Segments" on page 12-24). Like other permanent tablespaces, undo tablespaces contain data files. Undo blocks in these files are grouped in extents.
Automatic Undo Management Mode
        Undo tablespaces require the database to be in the default automatic undo management mode. This mode eliminates the complexities of manually administering undo segments. The database automatically tunes itself to provide the best possible retention of undo data to satisfy long-running queries that may require this data.
        An undo tablespace is automatically created with a new installation of Oracle Database. Earlier versions of Oracle Database may not include an undo tablespace and use legacy rollback segments instead, known as manual undo management mode. When upgrading to Oracle Database 11g, you can enable automatic undo management mode and create an undo tablespace. Oracle Database contains an Undo Advisor that provides advice on and helps automate your undo environment.
        A database can contain multiple undo tablespaces, but only one can be in use at a time. When an instance attempts to open a database, Oracle Database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo data in the SYSTEM tablespace. Storing undo data in SYSTEM is not recommended.

Automatic Undo Retention
        The undo retention period is the minimum amount of time that Oracle Database attempts to retain old undo data before overwriting it. Undo retention is important because long-running queries may require older block images to supply read consistency. Also, some Oracle Flashback features can depend on undo availability.
        In general, it is desirable to retain old undo data as long as possible. After a transaction commits, undo data is no longer needed for rollback or transaction recovery. The database can retain old undo data if the undo tablespace has space for new transactions. When available space is low, the database begins to overwrite old undo data for committed transactions.
        Oracle Database automatically provides the best possible undo retention for the current undo tablespace. The database collects usage statistics and tunes the retention period based on these statistics and the undo tablespace size. If the undo tablespace is configured with the AUTOEXTEND option, and if the maximum size is not specified, then undo retention tuning is different. In this case, the database tunes the undo retention period to be slightly longer than the longest-running query, if space allows.

Temporary Tablespaces
        A temporary tablespace contains transient data that persists only for the duration of a session. No permanent schema objects can reside in a temporary tablespace. The database stores temporary tablespace data in temp files.
        Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory. These tablespaces also improve the efficiency of space management operations during sorts.
        When the SYSTEM tablespace is locally managed, a default temporary tablespace is included in the database by default during database creation. A locally managed SYSTEM tablespace cannot serve as default temporary storage.

        Note:
                You cannot make a default temporary tablespace permanent.
        You can specify a user-named default temporary tablespace when you create a database by using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement. If SYSTEM is dictionary managed, and if a default temporary tablespace is not defined at database creation, then SYSTEM is the default temporary storage. However, the database writes a warning in the alert log saying that a default temporary tablespace is recommended.

Tablespace Modes
        The tablespace mode determines the accessibility of the tablespace.

        Read/Write and Read-Only Tablespaces
Every tablespace is in a write mode that specifies whether it can be written to. The mutually exclusive modes are as follows:
        ■Read/write mode
        Users can read and write to the tablespace. All tablespaces are initially created as read/write. The SYSTEM and SYSAUX tablespaces and temporary tablespaces are permanently read/write, which means that they cannot be made read-only.
        ■Read-only mode
        Write operations to the data files in the tablespace are prevented. A read-only tablespace can reside on read-only media such as DVDs or WORM drives.
        Read-only tablespaces eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces do not change and thus do not require repeated backup. If you recover a database after a media failure, then you do not need to recover read-only tablespaces.

Online and Offline Tablespaces
        A tablespace can be online (accessible) or offline (not accessible) whenever the database is open. A tablespace is usually online so that its data is available to users. The SYSTEM tablespace and temporary tablespaces cannot be taken offline.
        A tablespace can go offline automatically or manually. For example, you can take a tablespace offline for maintenance or backup and recovery. The database automatically takes a tablespace offline when certain errors are encountered, as when the database writer (DBW) process fails in several attempts to write to a data file. Users trying to access tables in an offline tablespace receive an error.
        When a tablespace goes offline, the database does the following:
        ■The database does not permit subsequent DML statements to reference objects in the offline tablespace. An offline tablespace cannot be read or edited by any utility other than Oracle Database.
        ■Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level.
        ■The database saves undo data corresponding to those completed statements in a deferred undo segment in the SYSTEM tablespace. When the tablespace is brought online, the database applies the undo data to the tablespace, if needed.

Tablespace File Size
        A tablespace is either a bigfile tablespace or a smallfile tablespace. These tablespaces are indistinguishable in terms of execution of SQL statements that do not explicitly refer to data files or temp files. The difference is as follows:
        ■A smallfile tablespace can contain multiple data files or temp files, but the files cannot be as large as in a bigfile tablespace. This is the default tablespace type.
        ■A bigfile tablespace contains one very large data file or temp file. This type of tablespaces can do the following:
        –Increase the storage capacity of a database
        The maximum number of data files in a database is limited (usually to 64 KB files), so increasing the size of each data file increases the overall storage.
        –Reduce the burden of managing many data files and temp files

        Bigfile tablespaces simplify file management with Oracle Managed Files and Automatic Storage Management (Oracle ASM) by eliminating the need for adding new files and dealing with multiple files.
        –Perform operations on tablespaces rather than individual files
        Bigfile tablespaces make the tablespace the main unit of the disk space administration, backup and recovery, and so on.
Bigfile tablespaces are supported only for locally managed tablespaces with ASSM. However, locally managed undo and temporary tablespaces can be bigfile tablespaces even when segments are manually managed.