TEMP_UNDO_ENABLED determines whether transactions within a particular session can have a temporary undo log.
Modifiable in a PDB
Range of values
Each session of each instance can have its own value or not set any value at all
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
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.
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.
Oracle Database Administrator’s Guide for information on managing temporary undo