Tablespaces And Datafiles

In today’s article, we will be discussing Tablespace, which is a logical container that allows us to manage datafile groups.

A tablespace can have one or more data files. A data file can belong to only one tablespace.

Data files are files with *.dbf extension on the disk where our data is stored.

SYSTEM, SYSAUX, USERS, UNDO and TEMP tablespaces are created by default with the installation. These tablespaces are the minimum required tablespace for a database to operate.

It is recommended to create separate tablespaces for an application’s table data and index data.

SYSTEM TABLESPACE

It is the tablespace where Data Dictionary objects are located. Data Dictionary is the place where the metadata of all information used in the database is kept.

Objects belonging to the SYS user are kept in this tablespace.

SYSAUX TABLESPACE

It is an auxiliary tablespace used as a data store for Oracle database tools such as OEM, Statspack, and Log Miner.

USERS TABLESPACE

It is a default tablespace created for user data. It is a permanent tablespace.

UNDO TABLESPACE

It is a tablespace used to retrieve data that is not intended to be made permanent.

It is the area where the data is kept before the insert, update and delete statement is executed.

TEMP TABLESPACE

Some SQL statements require sorting space on memory or disk for sorting and indexing. In this case, Oracle uses memory space first. If there is no space left in the memory, TEMP performs the sorting operation on the disk with the tablespace.

It is the default tablespace for each newly created user. It can be created later if desired.