Oracle 12c Global Temporary Tables & Undo & Redo…

Oracle-Magazin: http://www.oracle.com/technetwork/issue-archive/2013/13-nov/o63asktom-2034271.html

Starting in Oracle Database 12c, temporary undo can be stored in the temporary tablespace and undo for permanent objects can be stored in the undo tablespace. What this effectively means is that operations on temporary tables will no longer generate redo.

How undo is generated in Oracle Database 12c for global temporary tables is controlled by a new init.ora parameter: temp_undo_enabled. It has two settings: TRUE and FALSE. By default, this parameter is set to FALSE and undo will be generated in the same fashion it was in the past. For example:

SQL> alter session
set temp_undo_enabled = false;

Session altered.

SQL> insert into gtt
2 select *
3 from all_objects;
87310 rows created.

Statistics
———————————————————————————

566304 redo size

SQ> update gtt
2 set object_name =
lower(object_name);
87310 rows updated.

Statistics
————————————————————————————

8243680 redo size

As you can see, the INSERT generates about half a megabyte of redo (566,304 bytes) while the UPDATE generates upwards of 8 MB of redo (8,243,680 bytes). If I enable temporary undo, however:

SQL> alter session
set temp_undo_enabled = true;

Session altered.

SQL> insert into gtt
2 select *
3 from all_objects;
87310 rows created.

Statistics
———————————————————————————————

280 redo size

SQL> update gtt
2 set object_name =
lower(object_name);
87310 rows updated.
Statistics
———————————————————————————————

0 redo size