As we all know Oracle database always contains a set of system related tablespaces such as SYSTEM, SYSAUX, UNDO & TEMP, and each of them are used for different purposes within the Oracle database. Pre Oracle 12c R1, undo records generated by the temporary tables used to be stored in undo tablespace, much similar to a general/persistent table undo records. However, with the new feature introduced in 12c R1 temporary undo, the temporary undo records can now be stored in a temporary table instead of stored in undo tablespace.

Benefit of temporary undo

Reduction in undo tablespace and less redo data generation as all this information won’t be logged in redo logs. Also this feature is flexible and DBA can enable the temporary undo option either at session level or database level.

Enabling temporary undo

To enable this new feature, the following needs to be set:

Compatibility parameter must be set to 12.0.0 or higher
Initialization parameter TEMP_UNDO_ENABLED needs to be enabled
As described above all the temporary undo records are now stored in a temp tablespace, we need to create the temporary tablespace with sufficient space
For session level, we can use: ALTER SESSION SET TEMP_UNDO_ENABLE=TRUE;

Important V$ Views

The dictionary views listed below are used to view/query the information/statistics about the temporary undo data:

V$TEMPUNDOSTAT
DBA_HIST_UNDOSTAT
V$UNDOSTAT

If we would like to disable this feature, we need to execute the below statement:

SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=FALSE;