Oracle 参数 TEMP_UNDO_ENABLED 官方解释，作用，如何配置最优化建议
本站中文解释
TEMP_UNDO_ENABLED参数控制是否使用临时回滚段来减轻主回滚段压力，默认为FALSE，可以设置为TRUE。
临时回滚段的作用是：当向临时表插入数据时，该表的数据可能会存放在临时回滚段中，这意味着一个操作可以分解为两步：第一步是向临时回滚段中插入数据，第二步是将数据插入到临时表中。但如果不使用临时回滚段，只有一步定义：将数据插入到临时表中。
因此，为了减轻主回滚段压力，我们可以将TEMP_UNDO_ENABLED设置为TRUE，使用临时回滚段。但如果临时表不使用自动清理段（AUTOEXTEND UNDO段等），则不建议把TEMP_UNDO_ENABLED设置为TRUE，因为临时回滚段大小有限，可能会导致性能问题。
官方英文解释
TEMP_UNDO_ENABLED determines whether transactions within a particular session can have a temporary undo log.
| Property | Description |
|---|---|
|
Parameter type |
Boolean |
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
|
|
Basic |
No |
|
Oracle RAC |
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 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
