In 11g, when ever temporary tables are updated, undo and redo will be generated.undo tablespace holds the rollback data and online redo stores changed vector. Here data is logged into online redo and undo
In 12c, when ever parameter “ temp_undo_enabled”, Undo generated by temporary objects are written to Temp tablespace rather than using undo and redo which helps in performance of database
"temp_undo_enabled" is dynamic parameter, can be enabled and disabled as follows at database level or at session level
sql> alter session set temp_undo_enabled=true;
Session altered.
SQL> alter system set temp_undo_enabled=false;
System altered.
-- when we enable “temp_undo_enabled" parameter and application or user loading into the temporary tables or updating here is no redo generated by DML performed by global temporary tables
-- when ever you enable this parameter, increase the size of the temporary tablespace
—NOTE: To implement this parameter,database compatibility should be greater than equal to 12.0.0
we can monitor the tempundo usage by views "v$tempundostat".
stats for this particular view is updated for every 10 minutes
To avoid - snapshot too old errors, using "v$undostat" tune the parameter "undo_rentention" on the database
--Thank you--
--Nikhil Tatineni--
--12c-
