What is Temporary Undo?

Temp tables are widely used for preparing intermediate results. Modifying these tables is much faster than regular tables. The performance gain is mainly due to the absence of redo entries. Changes to temporary tables are created directly. However, redo logs are still generated for transactions in temporary tables. Undo for temporary tables is useful for consistent reads and transaction rollbacks during the temporary period.

Starting with Oracle Database 12, it is possible to store undo segments created by operations on temporary tables in the temp tablespace. This allows global temp tables to be used in physical standby databases and read-only databases, eliminating the need to recreate them. This mode is called temporary undo.

Temporary Undo Benefits:

• Temporary Undo reduces the amount of undo stored in undo tablespaces. Fewer undo tablespaces in an undo operation can result in more realistic undo retention period requirements for undo records.
• Temporary Undo reduces the redolog size. It improves performance because fewer redologs are generated and when parsing the redolog with tools like LogMiner, the parsing process will take less time because fewer redologs are involved.
• Temporary Undo enables DML operations on temporary tables in the standby database if Oracle Active DataGuard is configured. However, in order for DDL operations to occur in the standby database, they must be performed in the primary database.

Enabling Temporary Undo Feature:

You can enable temporary undo for a specific session or for the entire database. When temporary undo is enabled, a temporary undo is created for a session that uses the ALTER SESSION statement without affecting other sessions. When temporary undo is enabled for the system using the ALTER SYSTEM statement, temporary undo is created for all existing sessions and new sessions.

The first time a session uses temporary objects, the value of the TEMP_UNDO_ENABLED parameter is set for the rest of the session. Therefore, if temporary undo is enabled for a session and the session uses temporary objects, temporary undo cannot be disabled for the session. Similarly, if temporary undo is disabled for a session and the session uses temporary objects, temporary undo cannot be enabled for the session.

The Temporary Undo feature is available for databases with the COMPATIBLE parameter set to at least 12.1.0.0.0.

Temporary Undo is enabled by default for a physical standby database with Oracle Active Data Guard configured. Therefore, the TEMP_UNDO_ENABLED parameter has no effect on such standby databases.

To enable for a session:

SQL> ALTER SESSION SET temp_undo_enabled = true;

To enable for a database instance:

SQL> ALTER SYSTEM SET temp_undo_enabled = true;

When temporary undo is enabled, a session will start working with the first temp table used.

Monitoring Temporary Undo Usage:

V$TEMPUNDOSTAT displays various statistics related to temporary undo for a database instance. It displays a histogram of statistical data to show how the system is performing. Each row in the view is for a 10 minute period. The rows are in descending order of the BEGIN_TIME column value.

Some important columns of the V$TEMPUNDOSTAT view are:

BEGIN_TIME: Defines the beginning of the time interval
TXNCOUNT: Total number of transactions connected to the temporary undo segment in the relevant time interval
MAXCONCURRENCY: Maximum number of transactions that are executed simultaneously and modify temporary objects in the relevant time interval
UNDOBLKCNT: Total number of temporary undo blocks consumed in the relevant time interval
USCOUNT: Temporary undo segments created in the relevant time interval
NOSPACEERRCNT: Total time left until the error of no more free space for temporary undo occurs in the relevant time interval

You can also access various information using temporary undo from the views below.

• V$UNDOSTAT
• V$ROLLSTAT
• V$TRANSACTION
• DBA_UNDO_EXTENTS
• DBA_HIST_UNDOSTAT

You can see the temporary undo status with a query like the one below.

SELECT to_char(BEGIN_TIME,'dd/mm/yy hh24:mi:ss') "BEGIN TIME", TXNCOUNT "TXNCNT", MAXCONCURRENCY, UNDOBLKCNT, USCOUNT "USCNT", NOSPACEERRCNT "NOSPEERRCNT" FROM  V$TEMPUNDOSTAT;