Tablespaces, Datafiles, and Control Files
Oracle stores data locically in tablespaces and physically in datafiles associated with the corresponding tablespace.
Database, tablespaces, and datafiles are closely related, but they have important differences:
- An Oracle database consists of one or more logical storage usnits calles tablespaces, which collectively store all of the database's data.
- 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 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 on datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).
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 internally uses standard file system interfaces to create and delete files as needed for the following database structures: Tablespaces, Redo log files and Control files.
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
Overview of Tablespaces
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.
Bigfile Tablespaces - Oracle lets you create bigfile tablespace. This allows Oracle Database to contain tablespace 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.
Benefits of Bigfile Tablespaces: 1. Bigfile tablespaces can significantly increase the storage capacity of an Oracle database. 2. Bigfile tablespaces simplify management of datafiles in ultra large database 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. 3. They simplify database management by providing datafile transparency.
Considerations with Bigfile Tablespaces
- Bigfile tablespaces are intended to be used with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
- Avoid creating bigfile tablespaces on system that does not support striping because of negative implications for parallel execution and RMAN backup
- 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 new datafile.
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.
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 dtaabase creation or database upgrade.
Undo Tablespaces - 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. Each database contains zero or more undo tablespace. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.
Default Temporary Tablespace - When the 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.
If 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 ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in futrue release.
Specify default temporary tablespaces when you create a database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement. If you drop all default temporary tablespaces, then the SYSTEM tablespace is used as the default temporary tablespace.
Managing Space in Tablespaces - Tablespaces allocate space in extents. Tablespaces can use two different methods to keep track of their free and used space:
Locally managed tablespaces: Extent management by the tablespace
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_ADIN PL/SQL package.
Multiple Block Sizes - Oracle support s 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_SIXE. Legitimate values are from 2K to 32K.
In the initialization parameter file or server parameter, 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.
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.
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.
Transport of Tablespaces Between Databases - 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.
Overview of Datafiles
A tablespace in an Oracle database consists of one or more physical datafiles. A datafile can be associated with only on tablespace and only one database.
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 furture 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.
Size of Datafiles - You can alter 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.
Offline Datafiles - You can take tablespaces offline or bring them online at any time, execpt 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.
Temporary Datafiles - Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles, with the following exceptions:
- Tempfiles are always set to NOLOGGING mode.
- You cannot make a tempfile read only.
- You cannot create a tempfile with the ALTER DATABASE statement.
- 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.
- 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 V$DATAFILE view.
Overview of Control Files
The database control file is small binary file necessary for the database to start and operate successfully. A control file is updated continuously by Oracle 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 datab ase cannot function properly.
Control File Contents - Acontrol 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; no database administrator or user can edit a control file.
Among other things, a control file contains information such as:
- The datbase name
- The timestamp of database creation
- The names and locations of associated datafiles and redo log files.
- Tablespace information
- 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 current log sequence number
- Checkpoint information
