Oracle Storage Structures

Back to: Oracle DBA Tutorials

Oracle Database Storage Structures

In this article, I am going to discuss Oracle Database Storage Structures in Detail. Please read our previous article where we discussed Oracle Database Files in Detail. This is one of the most important topics.

Oracle Database Storage Structures:

The storage structure consists of two types.

1. Logical Storage Structures
2. Physical Storage Structures

Physical Storage System:

The data exists in datafiles. The data file should exist in the storage system. There are multiple storage systems.

1. SAN: Storage Area Network
2. NAS: Network Attached Storage
3. NFS: Network File System
4. ASM: Automatic Storage Management

This is mostly handled by the storage team and the network team. Whenever we discuss physical means of the storage files we can see the datafiles. Oracle manages these data files in a logical structure. The Physical data files are the files in the storage that we can see.

Logical Storage Structure:

Logical Structure is existing in the following model.

1. Oracle Data Block
2. Extent
3. Segment
4. Tablespace
5. Database

Let us discuss all the above in detail.

1. Oracle Data Block: Oracle data is stored in the form of DB blocks, 1 Block=8KB. We can change this manually. A single data block consists of one or more rows. This is the basic storage unit in logical storage.
2. Extent: Extent is the set of contiguous oracle data blocks. It is much more efficient when the allocation of space.
3. Segments: A segment is a set of extents. One or more extents are allocated for certain structures inside the database (Ex: Table. index). When we create a table inside the database means we are creating a table inside the index. It defines one table = one segment. One index = one segment. We can check the contents that are present under the segments by using dba segments. The container of the segments is present in the tablespace.
4. Tablespace: Tablespaces are logical storage groups that can be used to store logical database constructs such as tables and indexes. Logically it stores the database files. The relation between the data files and the tablespace is many to one. So, we can have many data files present.

Note: In the logical structure, the relation between tablespace and datafile is many to one. A tablespace is a container of segments. Segments contain objects like tables or indexes. The relation between tablespaces and databases is many to one. We could have many tablespaces belonging to one database.

What is Tablespace?

Tablespaces are logical storage groups that can be used to store logical database constructs such as tables and indexes. There are five types of tablespaces that are stored in the oracle database. They are as follows:

1. SYSTEM
2. SYSAUX
3. TEMP
4. UNDO
5. USERS

For a better understanding, please have a look at the below diagram.

We will explain general information about the tables given. These are the default tablespaces. This information will help to understand the further concepts better.

SYSTEM tablespace:

The SYSTEM tablespace is used for core functionality. It stores the data dictionary information means metadata (i.e. data about data). The data dictionary belongs to the sys schema. Oracle creates system tablespace automatically when the database is created. We cannot rename or drop the SYSTEM tablespace. We don’t create user data in the system tablespace.

SYSAUX tablespace:

The SYSAUX tablespace is the helper of the system tablespace. The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. It helps to reduce the load on the SYSTEM tablespace. Oracle creates it automatically when the database is created. We cannot rename or drop the SYSAUX tablespace.

TEMP tablespace:

The TEMP tablespace is used to manage space for database sort and joining operations and for storing global temporary tables. Oracle uses temporary tablespaces for sorting datasets that are too big to fit into PGA. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, and so on.

Example: When a user executes a select statement containing an order by oracle needs space to execute this statement and make an order for the select statement and display the output. Oracle uses the temporary tablespace for this operation. There should be a question Oracle also can use the PGA for this operation why only temp tablespace. These operations would require huge RAM and huge space so oracle uses temp tablespace for the above operations.

SELECT FROM STUDENTS ORDER BY ROLL_NO;

UNDO tablespace:

UNDO tablespace is used to roll back or undo, changes to the database. Roll back transactions when a ROLLBACK statement is issued. Recover the database back to the original state. Provides read consistency

Example: User runs an update statement and didn’t issue a commit yet. Oracle keeps the old value in the undo tablespace in order to recover the data if the user issues rollback and new values in the database buffer cache and redo log buffer. Once the user issues a commit then the old data present in the undo tablespace is removed and new data present in the database buffer cache and the redo log buffer will be updated to the datafiles. The other users can be able to access the old data which is not committed and the old data stored in the undo tablespace. The user may commit and may not commit. This is the reason UNDO tablespace is created. This provides read consistency.

USERS tablespace:

The USERS tablespace stores user’s objects and data. Here we can tables, insert data, and update data. Every database should have a tablespace for permanent user data that is assigned to users. Otherwise, user objects will be created in the SYSTEM tablespace, which is not good practice. In the preconfigured database, USERS is designated as the default tablespace for all new users. Once a new user is created oracle will allocate users tablespace as the default tablespace. If we want a new tablespace for a particular user you need to create a separate tablespace for the user.

Note: All these tablespaces exist in the container database and also the pluggable databases. We will discuss the container and pluggable database as further topics.

In the next article, I am going to discuss Oracle Multitenant Architecture 12c/18c in detail. Here, in this article, I try to explain Oracle Storage Structures and I hope you enjoy this Oracle Storage Structures article.