Skip Headers
Oracle® Database Reference
12c Release 1 (12.1)

Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub


Property Description
Parameter type Boolean
Default value false
Range of values true | false
Basic No
Oracle RAC Each session of each instance can have its own value or not set any value at all

TEMP_UNDO_ENABLED determines whether transactions within a particular session can have a temporary undo log. The default choice for database transactions has been to have a single undo log per transaction. This parameter, at the session level / system level scope, lets a transaction split its undo log into temporary undo log (for changes on temporary objects) and permanent undo log (for changes on persistent objects).

By splitting the undo stream of a transaction into two streams (temporary and permanent), a database can provide separate storage and retention model for these. This results in overall reduction in the size of undo log and redo log in the database

If database applications make use of temporary objects (using global temporary tables or temporary table transformations), it is advisable to set this parameter's value to true.

When TEMP_UNDO_ENABLED is set to true and the COMPATIBLE initialization parameter is set to 12.0.0, this feature is enabled. The temporary undo feature is enabled for the session in which it is set. Setting it across the system will affect all existing and upcoming sessions. If the value is set in the init.ora file, all upcoming sessions will inherit this value unless overwritten by an explicit ALTER SESSION or ALTER SYSTEM statement. All undo for operations on temporary objects is deemed temporary.

If TEMP_UNDO_ENABLED is not set to true, existing applications that make use of temporary objects run as is without any change.

Once the value of the parameter is set, it cannot be changed for the lifetime of the session. If the session has temporary objects using temporary undo, the parameter cannot be disabled for the session. Similarly, if the session already has temporary objects using regular undo, setting this parameter will have no effect.

This parameter is only applicable for the primary database. For a standby database, this parameter is ignored because temporary undo is enabled by default on the standby database.

See Also:

Oracle Database Administrator's Guide for information on managing temporary undo