Undo space can be a problem for the database, especially if the application code is written in such a way that it holds transactions open for a long time (generally a bad practice) or there are long-running queries in the database. If the undo tablespace runs out of space, the database will generate ORA-01650: Unable to extend rollback segment for attempts to update the database and ORA-01555: Snapshot too old for long-running queries. Each of these will cause application errors, resulting in unhappy users.
- Enabling temp undo helps with both of these problems by removing all undo for temporary tables from the database undo tablespace, giving more space for DML operations.
- Undo retention will more accurately reflect the database needs and not be skewed by temporary table usage. This may help meet retention targets for FLASHBACK operations.
- Less redo is generated against the database as well. Temporary table DML does not generate redo so rollbacks against temporary tables do not generate redo either.
- Operations that use undo (database recovery, Log Miner, etc.) will complete faster since the temp undo is not included.
- This setting affects operations against temporary tables only. Other uses of the temporary tablespaces (sorts, index creations, select distinct, etc.) will not be affected.
- The first time a session uses temporary tables, the current value of TEMP_UNDO_ENABLED will be used for the rest of the session and cannot be changed.
