Sunday, 13 May 2012

Relation between Tablespace, Datafile and Control File

Databases, tablespaces, and datafiles are closely related, but they have important differences:

An Oracle database consists of one or more logical storage units called 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 one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).


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.

Default Tablespaces: System, SysAux, Undo and Temporary

Other Tablespaces: Bigfile, Read-only, Temporary Tablespaces for Sort Operation

1. Tablespaces can be made online and offline.
2. Tablespaces can be transported from one database to another.


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 future 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.

Control Files:

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 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.

No comments:

Post a Comment