Oracle Database - Data Files
About
Every Oracle database has one or more physical datafiles, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.
Datafiles have the following characteristics:
- One or more datafiles form a logical unit of database storage called a tablespace.
- A datafile can be associated with only one tablespace.
- Datafiles can be defined to extend automatically when they are full.
Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle Database. For example, if a user wants to access some data in a table of a database, and if the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory.
Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the background process database writer process (DBWn).
Datafiles that are stored in temporary tablespaces are called tempfiles and are subject to some restrictions.
Articles Related
Tablespace and data file
| Tablespace | Data File | Description |
|---|---|---|
| EXAMPLE | EXAMPLE01.DBF | Stores the Sample Schemas, if you included them. |
| SYSAUX | SYSAUX01.DBF | Serves as an auxiliary tablespace to the SYSTEM tablespace. Some products and options that previously used the SYSTEM tablespace now use the SYSAUX tablespace to reduce the load on the SYSTEM tablespace. |
| SYSTEM | SYSTEM01.DBF | Stores the data dictionary, including definitions of tables, views, and stored procedures needed by the Oracle Database. Information in this area is maintained automatically. |
| TEMP | TEMP01.DBF | temporary_tablespace: Stores temporary tables and indexes created during the processing of your SQL statement. If you are running a SQL statement that involves a lot of sorting, such as the constructs GROUP BY, ORDER BY, or DISTINCT, then you may need to expand this tablespace. |
| UNDOTBS | UNDOTBS01.DBF | Stores undo information. The undo tablespace contains one or more undo segments that maintain transaction history that is used to roll back, or undo, changes to the database. All starter databases are configured to run in automatic undo management mode. |
| USERS | USERS01.DBF | Stores database objects created by database users. |
Data files
In the Data Dictionary
SELECT * FROM DBA_DATA_FILES; SELECT * FROM v$datafile; SELECT * FROM V$TEMPFILE;
In Oracle Enterprise Manager Database Control
Log in to the Database Control > Server > In the Storage section of the Server page, click Datafiles.
Data Files Identification
To uniquely identify a data file, Oracle Database assigns each datafile two associated file numbers:
- an absolute file number
Uniquely identifies a datafile in the database. This file number can be used in many SQL statements that reference datafiles in place of using the file name. The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view, or in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view. Tempfiles are a special class of datafiles that are associated only with temporary tablespaces.
- and a relative file number
Uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number. In a bigfile tablespace, the relative file number is always 1024 (4096 on OS/390 platform).
