This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions


Temporary Undo

blessed DBA
blessed DBA Member Posts: 218
edited Mar 16, 2018 4:37AM in Database Ideas - Ideas

Each Oracle database contains a set of system related tablespaces, such as, SYSTEM, SYSAUX, UNDO & TEMP, and each 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 temporary undo feature in 12c R1, the temporary undo records can now be stored in a temporary table instead of stored in undo tablespace. The prime benefits of temporary undo includes: reduction in undo tablespace and less redo data generation as the information won’t be logged in redo logs. You have the flexibility to enable the temporary undo option either at session level or database level.

Enabling temporary undo

To be able to use the new feature, the following needs to be set:

  • Compatibility parameter must be set to 12.0.0 or higher
  • Enable TEMP_UNDO_ENABLED initialization parameter
  • Since the temporary undo records now stored in a temp tablespace, you need to create the temporary tablespace with sufficient space
  • For session level, you can use: ALTER SESSION SET TEMP_UNDO_ENABLE=TRUE;

To disable the feature, you simply need to set the following:

0 votes

Active · Last Updated