Managing the undo tablespace - Oracle DBA

This post describes managing the undo tablespace - oracle dba which stores information used to roll back changes to the oracle database. It contains the following topics:

If you would like to Enrich your career with a Database Administrator(DBA) and get Oracle certified professional, then visit Mindmajix - A Global online training platform: Oracle DBA Online Course ”  Course.  This course will help you to achieve excellence in this domain.

Managing the undo tablespace 

What is undo?

Every Oracle database must have a method of maintaining information that is used to roll back or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

[Related Article: Oracle DBA Tutorial]

Undo records are used to:

  • Rollback transactions when a rollback statement is issued
  • Recover the database
  • Provide read consistency
  • Analyze data as of an earlier point in time by using oracle flashback query
  • Recover from logical corruptions using oracle flashback features

 MindMajix YouTube Channel

When a rollback statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before the image of the data for users who are accessing the data at the same time that another user is changing it.

[Related Article: Managing the Redo Log]

Introduction to automatic undo management:

This section introduces the concepts of automatic undo management and discusses the following topics:

Overview of automatic undo management:

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing to undo information and space. In this management model, you create an undo tablespace, and the server automatically manages to undo segments and space among the various active sessions.

When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the system tablespace. This is not recommended in normal circumstances, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace.

[Related Article: Oracle DBA Interview Questions]

Undo_tablespace = undotbs_01

In this case, if you have not already created the undo tablespace (in this example, undotbs_01), the startup command fails. The undo_tablespace parameter can be used to assign a specifically undo tablespace to an instance in an oracle real application clusters environment.

[Related Article: Oracle Apps DBA Interview Questions]

The following is a summary of the initialization parameters for automatic undo management:

Initialization parameter Description
Undo_management If auto, use automatic undo management. The default is manual.

An optional dynamic parameter specifying the name of an undo tablespace. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.


Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!


Course Schedule
Oracle DBA TrainingJun 25 to Jul 10View Details
Oracle DBA TrainingJun 29 to Jul 14View Details
Oracle DBA TrainingJul 02 to Jul 17View Details
Oracle DBA TrainingJul 06 to Jul 21View Details
Last updated: 04 Apr 2023
About Author


Technical Content Writer

read less