Temporary Undo in Oracle 12C

Share via:

Dear Readers,

In this article, we will see the following Temporary Undo in Oracle 12C.

Temporary Undo in 12C

Undo tablespaces facilitate the rollback of logical transactions. In addition, undo tablespaces support a number of other features, including read consistency, various database-recovery operation, and flashback functions.

A database may have more than one undo tablespace, although only one can be active at a time.

Oracle’s best practice suggest that you create a single undo tablespace per instance that is large enough to handle all transaction loads, in other words, “Set it and forget it”.

Storing Undo in Temporary Tablespace: Oracle database 12c introduces a new option for using undo: temporary undo. Although temporary table were available in previous versions of oracle database and enhanced performance because no redo operations were generated by DML against temporary table.

The temporary undo does not have to reside in the database’s default undo tablespace. Instead, the undo for temporary tables can reside in the temporary tablespace itself and therefore not generate addition vectors in the online redo log files. To use temporary undo, the COMPATIBLE initialization parameter must be set to at least

Why Temp Undo?

  • The performance gain is mainly due to the fact that no redo entries are going to generate for changes on temporary tables
  • Temporary objects and temporary transactions are not required to be saved
  • Redo logs files are used to store permanent undo
  • It reduces the amount of Undo stored in the undo tablespaces
  • It reduces the size of the redo logs
    As we know temporary undo use temporary tablespace so you need to create temporary tablespace with sufficient size.

Below figure shows the location for undo data for both persistent and temporary table.

Temporary undo can also be enabled at the session level. To enable temporary undo at the database level, change the initialization parameter TEMP_UNDO_ENABLED.Let’s go through the process of seeing how to setup temporary undo and how to actually monitor it.

We can enable temp_undo in two ways. They are :

1.Session Level

2.System Level


Session Level :

Only for current instance.Once Oracle server reboots then Temp_undo will be Disable.

To set session level follow below command.

System Level

This system level will be current and  for future instances as well until we changes.

To set system level follow below command.

Check Temp_undo enabled or not .

V$TEMPUNDOSTAT use for to see the temporary undo log detail for the current database instance. This views collect statistics in a 10 minutes interval.

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

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform


Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)

Add Comment