TEMP UNDO TABLESPACE – Technology Geek

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

Tag: TEMP UNDO TABLESPACE

TEMP UNDO TABLESPACE : Another 12C Feature