Guide to UNDO Tablespaces and UNDO Segments

To properly operate an Oracle database, understanding the UNDO tablespace and UNDO segments is essential. This article is designed to be practical, incorporating plenty of specific examples and diagrams to help beginners learn the fundamentals and solve problems they may face in operations.

1.What is UNDO? (From Basics to Application)

UNDO is a mechanism that retains the “original state” of changes made by transactions, ensuring database integrity and consistency.

Main Uses of UNDO

Transaction Rollback Reverts incomplete changes.
Ensuring Query Consistency Returns stable query results even while other sessions are updating data.
Flashback Operations References or restores past states of data.

2.Overview of the UNDO Tablespace

The UNDO tablespace is a dedicated area for storing UNDO information. In Automatic UNDO Management mode, Oracle efficiently manages the UNDO tablespace.

Features of the UNDO Tablespace

Automatic UNDO Management: Enabled by default. Significantly reduces the administrative burden.
UNDO Retention Period: Retains UNDO information for a specified time.
UNDO Tablespace Size Management: Must be set appropriately according to the transaction volume.

UNDO Tablespace Configuration Image

[UNDO Tablespace]
   ├─ UNDO Segment 1
   ├─ UNDO Segment 2
   ├─ UNDO Segment 3
   ...

How to Check the UNDO Tablespace

SQL to check the currently used UNDO tablespace:

SELECT tablespace_name
FROM dba_tablespaces
WHERE contents = 'UNDO';

3.How UNDO Segments Work

UNDO segments are the units that actually store the UNDO information. The necessary UNDO segment is automatically allocated for each transaction.

Features of UNDO Segments

Dynamic Management: Oracle automatically allocates UNDO segments.
Concurrency Support: Multiple sessions can use different UNDO segments simultaneously.

How to Check UNDO Segments

SELECT segment_name, tablespace_name, status
FROM dba_rollback_segs;

Explanation of Statuses:

ONLINE: In use
OFFLINE: Not in use
PENDING OFFLINE: Being taken offline

4.Practice: How to Configure and Check UNDO

Creating an UNDO Tablespace

Command to create a new UNDO tablespace:

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/app/oracle/oradata/undotbs2.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

Switch the default UNDO tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

Setting the UNDO Retention Period

Set the retention period (in seconds) for UNDO information:

ALTER SYSTEM SET UNDO_RETENTION = 1200; -- Retain for 20 minutes

5.Troubleshooting (with Diagrams)

Problem 1: Insufficient UNDO Tablespace

Error: ORA-30036: Insufficient UNDO tablespace Cause: High volume of transactions, leading to insufficient space to store UNDO information. Solutions:

Extend the UNDO tablespace:SQLALTER DATABASE DATAFILE '/u01/app/oracle/oradata/undotbs1.dbf' RESIZE 2G;
Create and switch to a new UNDO tablespace:SQLCREATE UNDO TABLESPACE undotbs3 DATAFILE '/u01/app/oracle/oradata/undotbs3.dbf' SIZE 1G; ALTER SYSTEM SET UNDO_TABLESPACE = undotbs3;

Problem 2: UNDO Information is Not Retained

Cause: The UNDO retention period is too short. Solution: Reset the UNDO retention period:

ALTER SYSTEM SET UNDO_RETENTION = 1800; -- Retain for 30 minutes

6.Case Study: Solving Problems Using UNDO

Case 1: Checking Data with Flashback Query

Use Flashback Query to check the data state from 5 minutes ago:

SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

Case 2: Real-time Response to UNDO Shortage

Immediately extend the UNDO tablespace after the error occurs:

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/undotbs1.dbf' RESIZE 5G;

7.FAQ: Frequently Asked Questions and Answers

Q1: What is the optimal size for the UNDO tablespace? A1: It depends on the transaction volume and retention period. Check the recommended size with the following command:

SELECT dbms_undo_adv.required_undo_size(900) AS recommended_size
FROM dual;

Q2: Should I switch to manual UNDO management mode? A2: You should normally use Automatic UNDO Management mode. Manual management is not recommended unless you have special requirements.

8.Summary and Next Steps

Key points for successful operation of UNDO tablespaces and segments:

Set the UNDO tablespace size appropriately: Adjust the size according to transaction volume.
Optimize the UNDO retention period: Configure it according to database requirements.
Understand troubleshooting procedures in advance: Respond quickly when errors occur.

Based on what you’ve learned in this article, let’s further develop advanced operational skills. Try tackling “Flashback Operations” or “Query Optimization” next!

[reference]
Managing Undo – Database Administrator’s Guide