Overview

This is the method to replace the UNDO TABLESPACE when there is an issue.

Method

Preparation and Precautions

• Privileges: SYS privileges are required.

• Disk Space: Ensure there is sufficient disk space available to store the new UNDO data file.

• Backup: It is recommended to perform a full backup before making changes.

Check Current UNDO Tablespace

Check the name of the currently used UNDO tablespace and related data files

SELECT name FROM v$tablespace WHERE type = 'UNDO';

Check the list of data files in the UNDO tablespace

SELECT file_id, file_name, bytes, autoextensible 
FROM dba_data_files WHERE tablespace_name = '<current_UNDO_NAME>';

Create New UNDO TABLESPACE

CREATE UNDO TABLESPACE undo_new DATAFILE '/path/to/undo_new_01.dtf' SIZE 2G

AUTOEXTEND ON NEXT 100M MAXSIZE 20G;

Switch UNDO Online

ALTER SYSTEM SET UNDO_TABLESPACE = undo_new;

Verify Current UNDO Tablespace Application

SELECT name FROM v$tablespaces WHERE type = 'UNDO';

Check Data File Status

SELECT file_id, file_name, bytes, autoextensible

FROM dba_data_files WHERE tablespace_name = 'UNDO_NEW';

Check Active Transactions/Undo Usage

SELECT sid, serial#, start_time, state FROM v$transaction;