Overview of Data Files
At the operating system level, Oracle Database stores database data in structures called data files. Every Oracle database must have at least one data file.
Use of Data Files
Oracle Database physically stores tablespace data in data files.
Each nonpartitioned schema object and each partition of an object is stored in its own segment, which belongs to only one tablespace. For example, the data for a nonpartitioned table is stored in a single segment, which in turn is stored in
one tablespace. Tablespaces and data files are closely related, but have important differences:
• Each tablespace consists of one or more data files, which conform to the operating system in which Oracle Database is running.
• The data for a database is collectively stored in the data files located in each tablespace of the database.
• A segment can span one or more data files, but it cannot span multiple tablespaces.
• A database must have the SYSTEM and SYSAUX tablespaces. Oracle Database automatically allocates the first data files of any database for the SYSTEM tablespace during database creation.
The SYSTEM tablespace contains the data dictionary, a set of tables that contains database metadata. Typically, a database also has an undo tablespace and a temporary tablespace (usually named TEMP).
Permanent and Temporary Data Files
A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.
A temporary tablespace contains schema objects only for the duration of a session.
Locally managed temporary tablespaces have temporary files (temp files), which are special files designed to store data in hash, sort, and other operations. Temp files also store result set data when insufficient space exists in memory.
Temp files are similar to permanent data files, with the following exceptions:
• Permanent database objects such as tables are never stored in temp files.
• Temp files are always set to NOLOGGING mode, which means that they never have redo generated for them. Media recovery does not recognize temp files.
• You cannot make a temp file read-only.
• You cannot create a temp file with the ALTER DATABASE statement.
• When you create or resize temp files, they are not always guaranteed allocation of disk space for the file size specified. On file systems such as Linux and UNIX, temp files are created as sparse files. In this case, disk blocks are allocated not at file creation or resizing, but as the blocks are accessed for the first time.
• Temp file information is shown in the data dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.
Online and Offline Data Files
Every data file is either online (available) or offline (unavailable).
You can alter the availability of individual data files or temp files by taking them offline or bringing them online. The database cannot access offline data files until they are brought online.
You may take data files offline for many reasons, including performing offline backups or block corruption. The database takes a data file offline automatically if the database cannot write to it.
Like a data file, a tablespace itself is offline or online. When you take a data file offline in an online tablespace, the tablespace itself remains online. You can make all data files of a tablespace temporarily unavailable by taking the tablespace itself offline.
Starting in Oracle Database 12c, you can use the ALTER DATABASE MOVE DATAFILE statement to move an online data file from one physical file to another while the database is open and accessing the file. You can use this technique to achieve the
following goals:
• Move a tablespace from one kind of storage to another
• Move data files that are accessed infrequently to lower cost storage
• Make a tablespace read-only and move its data files to write-once storage, such as a write once read many (WORM) drive
• Move a database into Oracle ASM
Data File Structure
Oracle Database creates a data file for a tablespace by allocating the specified amount of disk space plus the overhead for the data file header. The operating system under which Oracle Database runs is responsible for clearing old information and authorizations from a file before allocating it to the database.
The data file header contains metadata about the data file such as its size and checkpoint SCN. Each header contains an absolute file number, which uniquely identifies the data file within the database, and a relative file number, which uniquely identifies a data file within a tablespace.
When Oracle Database first creates a data file, the allocated disk space is formatted but contains no user data. However, the database reserves the space to hold the data for future segments of the associated tablespace. As the data grows in a tablespace, Oracle Database uses the free space in the data files to allocate extents for the segment.
The following figure illustrates the different types of space in a data file. Extents are either used, which means they contain segment data, or free, which means they are available for reuse. Over time, updates and deletions of objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is called fragmented free space.
