An Undo tablespace stores undo records generated by DML operations (INSERT, UPDATE, DELETE). These records are used to: - Roll back uncommitted transactions Roll back uncommitted transactions - Provide read consistency for queries Provide read consistency for queries - Support flashback features like and Support flashback features like and - Recover transactions during instance recovery Recover transactions during instance recovery

Oracle offers two undo management modes: - Manual Undo Management (Deprecated) Uses rollback segments manually created and managed by DBAs. Obsolete from Oracle 9i onwards. Manual Undo Management (Deprecated) - Uses rollback segments manually created and managed by DBAs. Uses rollback segments manually created and managed by DBAs. - Obsolete from Oracle 9i onwards. Obsolete from Oracle 9i onwards. - Automatic Undo Management (AUM) (Recommended) Managed automatically by Oracle. You only need to create and assign undo tablespaces. Controlled by the initialization parameter: Automatic Undo Management (AUM) (Recommended) - Managed automatically by Oracle. Managed automatically by Oracle. - You only need to create and assign undo tablespaces. You only need to create and assign undo tablespaces. - Controlled by the initialization parameter: Controlled by the initialization parameter:

When a transaction modifies data: - Old values are written to the undo tablespace . Old values are written to the undo tablespace . - If the transaction is rolled back, Oracle restores these old values. If the transaction is rolled back, Oracle restores these old values. - Queries that started before the change can still see the consistent old data. Queries that started before the change can still see the consistent old data. Oracle automatically reuses undo space when it’s no longer needed, based on undo retention .

Run the following SQL as SYSDBA :

If you want to switch the active undo tablespace: You can then drop the old one (after verifying it’s not in use):

To check which undo tablespace is active: Check undo usage:

Undo retention determines how long undo data is preserved after a transaction commits. Set it using: If you want Oracle to strictly keep undo data for 900 seconds:

Use Automatic Undo Management ( ) Create separate undo tablespaces for different PDBs in Multitenant environments Enable AUTOEXTEND for undo datafiles Monitor undo space with and Periodically resize undo tablespaces based on workload trends

In Oracle RAC , each instance must have its own undo tablespace: This ensures proper transaction isolation between nodes.