Creating and Changing Undo Tablespaces in Oracle 12c

YouTube video

Introduction

In this article, we will discuss the concept of undo tablespaces in Oracle 12c. We will cover the following topics: creating an undo tablespace, changing the undo tablespace, and setting and guaranteeing retention with the undo tablespace. Additionally, we will explore some important data dictionary views that provide information about table spaces and data files.

Creating an Undo Tablespace

To create an undo tablespace, we use the command CREATE UNDO TABLESPACE followed by the name of the undo tablespace. Let’s say we already have an undo tablespace named “undo TBS one” and we want to create another one. We will create an undo tablespace named “undo TBS two” with the following command:

CREATE UNDO TABLESPACE undo TBS two
DATAFILE 'u01/APP/Oracle/aura/data/focus1/undo_01.TBF'
SIZE 20m;

Here, we specify the name of the undo tablespace as “undo TBS two”. We also provide the location and name of the data file where the undo tablespace will be stored. In this case, the data file is stored at ‘u01/APP/Oracle/aura/data/focus1/undo_01.TBF’. Additionally, we set the size of the tablespace to 20 megabytes.

After executing this command, the undo tablespace named “undo TBS two” will be created.

Changing the Undo Tablespace

Although we have created the undo tablespace “undo TBS two”, all the users are still using the existing undo tablespace “undo TBS one”. To change this and make the users utilize the new undo tablespace, we need to modify the system parameter value.

To change the undo tablespace, we use the command ALTER SYSTEM SET UNDO TABLESPACE = undo TBS two. This command alters the system parameter to set the undo tablespace to “undo TBS two”.

After executing this command, all the users will start using the new undo tablespace “undo TBS two” instead of “undo TBS one”.

Setting and Guaranteeing Retention

In Oracle, we can set a retention guarantee for undo data in the undo tablespace. This ensures that the undo data remains available for a specific period, even if space is limited.

To set the retention guarantee for the undo tablespace, we use the command ALTER TABLESPACE undo TBS two RETENTION GUARANTEE. This command enables the retention guarantee for the undo tablespace “undo TBS two”.

By setting the retention guarantee, Oracle ensures that the undo data in the undo tablespace “undo TBS two” is retained for the specified period, even if the space becomes limited. This is useful in scenarios where we need to rely on the availability of undo data for an extended period of time.

Data Dictionary Views for Tablespaces and Data Files

To view information about table spaces and data files, Oracle provides various data dictionary views. These views offer insights into the structure and details of the tablespaces and their associated data files.

Some important data dictionary views related to tablespaces and data files are:

  • DBA_TABLESPACES: This view provides information about all the tablespaces in the database, including their names, data file details, and the size of each tablespace.

  • DBA_DATA_FILES: This view provides information about all the data files in the database, including their names, sizes, and the associated tablespace for each data file.

  • DBA_TEMP_FILES: This view provides information about all temporary files in the database, including their names, sizes, and the associated temporary tablespace for each temporary file.

  • DBA_FREE_SPACE: This view provides information about the free space available in each tablespace, helping us analyze the space utilization and plan for any necessary actions to manage the tablespaces efficiently.

These data dictionary views are valuable tools for database administrators as they offer insights into the tablespaces and data files, allowing them to effectively manage and optimize the storage in the Oracle 12c database.

Conclusion

In this article, we discussed the creation and modification of undo tablespaces in Oracle 12c. We learned how to create a new undo tablespace, change the undo tablespace for user sessions, set and guarantee retention for undo data, and explored important data dictionary views related to tablespaces and data files. Using these techniques, database administrators can efficiently manage undo tablespaces and ensure proper retention and availability of undo data in the Oracle 12c database.