Temporary undo records are stored in the database's temporary tablespaces and thus are not logged in the redo log. When temporary undo is enabled, some of the segments used by the temporary tablespaces store the temporary undo, and these segments are called temporary undo segments. When temporary undo is enabled, it might be necessary to increase the size of the
temporary tablespaces to account for the undo records. Enabling temporary undo provides the following benefits:
- Temporary undo reduces the amount of undo stored in the undo tablespaces. Less undo in the undo tablespaces can result in more realistic undo retention period requirements for undo records.
- Temporary undo reduces the size of the redo log. Performance is improved because less data is written to the redo log, and
components that parse redo log records, such as LogMiner, perform better because there is less redo data to parse.
- Temporary undo enables data manipulation language (DML) operations on temporary tables in a physical standby database with the Oracle Active Data Guard option. However, data definition language (DDL) operations that create temporary tables must be issued on the primary database.
You can enable temporary undo for a specific session or for the whole system. When you enable temporary undo for a session using an ALTER SESSION statement, the session creates temporary undo without affecting other sessions. When you enable temporary undo for the system using an ALTER SYSTEM statement, all existing sessions and new sessions create temporary undo.