Tablespaces and Stored Structures in Oracle

Back to: Oracle DBA Tutorials

Tablespaces in Oracle

In this article, we will be creating and practicing tablespaces in Oracle. Before going into this article, we will review some information about tablespaces and storage structures.

Storage Structures in Oracle:

This is the most important topic. The storage structure consists of two types.

1. Logical Storage Structures
2. Physical Storage Structures

Physical Storage System in Oracle:

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

• SAN: Storage Area Network
• NAS: Network Attached Storage
• NFS: Network File System
• 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 data files. 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 in Oracle:

Logical Structure is existing in the following model.

• Oracle data block
• Extent
• Segment
• Tablespace
• Database

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.

Extent: Extent is the set of contiguous Oracle data blocks. It is much more efficient when allocating space.

Segments in Oracle:

A segment is a set of extents. One or more extents are allocated for certain structures inside the database (Ex: Table. index). When creating a table inside the database means we are creating a table inside the index. It defines one table as 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.

Tablespace in Oracle:

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.

What is Tablespace in Oracle?

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 stores in the oracle database.

Types of Tablespaces in Oracle:

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

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: 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: 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: 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 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: 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 its original state. Provides read consistency

Example: The user runs an update statement and did not 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 the 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: 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 the user’s 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 How Table Data is Stored in Oracle with Examples. Here, in this article, I try to explain Tablespaces in Oracle. I hope you enjoy this Tablespaces in Oracle article.