Monday, July 1, 2013

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

1 comment:

  1. hi i got a problem abt oracle database .can u plz solve this??

    ReplyDelete