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 12.1.0.0.0.
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.
|
1 2 3 4 5 6 7 8 |
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 |
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.
|
1 2 |
SQL> alter session set temp_undo_enabled=true; Session altered. |
System Level
This system level will be current and for future instances as well until we changes.
To set system level follow below command.
|
1 2 |
SQL> alter system set temp_undo_enabled=true; System altered. |
Check Temp_undo enabled or not .
|
1 2 3 4 5 6 7 |
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean TRUE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> select to_char(BEGIN_TIME, 'DD-MON-YY HH24:MI:SS' ), TXNCOUNT, MAXCONCURRENCY, UNDOBLKCNT, USCOUNT, NOSPACEERRCNT from V$TEMPUNDOSTAT; TO_CHAR(BEGIN_TIME TXNCOUNT MAXCONCURRENCY UNDOBLKCNT USCOUNT NOSPACEERRCNT ------------------ ---------- -------------- ---------- ---------- ------------- 20-JAN-19 20:47:17 6 0 1609 1 0 20-JAN-19 20:37:17 0 0 0 0 0 20-JAN-19 20:27:17 5 0 2 2 0 20-JAN-19 20:07:17 0 0 0 0 0 20-JAN-19 19:57:17 35 0 1 1 0 20-JAN-19 19:47:17 28 0 1 1 0 20-JAN-19 19:37:17 1 0 1 1 0 20-JAN-19 19:27:17 17 0 3 3 0 20-JAN-19 19:07:17 0 0 0 0 0 9 rows selected. |
To disable the feature, you simply need to set the following:
|
1 |
ALTER SESSION/SYSTEM SET TEMP_UNDO_ENABLED=FALSE; |
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
(4 votes, average: 4.75 out of 5)
Loading...
