TEMP UNDO TABLESPACE : Another 12C Feature

Testing is not finished yet , Everyday working on database 12C  , Lot Of New Features , TEMP_UNDO_TABLESPACE one of these new features, In older Oracle Versions Temp Tables are stored in Undo Tablespace, But now with #DB12c you can enable Parameter TEMP_UNDO_TABLESPACE by Default this parameter set to FALSE But in Dataguard 12c  this parameter is set to TRUE by default.

The Benefit of Using Temp Undo Tablespace :

  • reduce the amount of using undo tablespace
  • in this case performance improved because less data will be written on redolog.
  • Temp Undo Tablespace support DML 

SQL> show parameter TEMP_UNDO_ENABLED;

NAME     TYPE VALUE
———————————— ———– ——-
temp_undo_enabled     boolean FALSE

You Don’t have to reset Database to Enable this Parameter because it’s working on Session level 

SQL> alter session set temp_undo_enabled=TRUE   ;
Session altered.

also you set this parameter on database level :

SQL> alter system set temp_undo_enabled=true;
System altered.

SQL> show parameter temp_undo_enabled ;

NAME     TYPE VALUE
———————————— ———– ——-
temp_undo_enabled     boolean TRUE
 

View Related to temp undo tablespace :

SQL> desc V$TEMPUNDOSTAT;
 Name   Null?    Type
 —————————————– ——– —————————-
 BEGIN_TIME    DATE
 END_TIME             DATE
 UNDOTSN             NUMBER
 TXNCOUNT             NUMBER
 MAXCONCURRENCY     NUMBER
 MAXQUERYLEN    NUMBER
 MAXQUERYID    VARCHAR2(13)
 UNDOBLKCNT    NUMBER
 EXTCNT             NUMBER
 USCOUNT             NUMBER
 SSOLDERRCNT    NUMBER
 NOSPACEERRCNT     NUMBER
 CON_ID             NUMBER

2 thoughts on “TEMP UNDO TABLESPACE : Another 12C Feature

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.