Managing the undo tablespace:

This post describes how to manage the undo tablespace, which stores information used to roll back changes to the oracle database. It contains the following topics:

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.

Undo records are used to:

  • Roll back 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

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 image of the data for users who are accessing the data at the same time that another user is changing it.

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.

Subscribe to our youtube channel to get new updates..!

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 undo information and space. In this management mode, you create an undo tablespace, and the server automatically manages 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.

Checkout 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 specific undo tablespace to an instance in an oracle real application clusters environment.

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!