We all know that the operations such as INSERT, UPDATE, MERGE, and DELETE on global temporary tables generated redo. Just to clarify this for newbies:-

A Global Temporary Table stores its data in a Temporary Tablespace where the data is retained for the duration of either a single transaction or the lifetime of a session. Performing DML on a Global Temporary Table doesn’t generate redo, because the data is stored in a temporary tablespace, but it does generate undo which in turn will generate redo since the undo is stored in a permanent tablespace.

Now the question is that as this REDO isn’t required for recovery purposes whats the point of writing all that stuff to redo logs i.e. why does the changes in temporary tablespace generate undo ? If Oracle wrote a huge code to log these changes there would be some reason, right ? Any guesses ?

The reason is that the undo for global temporary tablespace is required as Oracle needs it in cases an application issues a roll back and Oracle has to provide a read consistent image. For situations in which a developer inserts some information into a global temporary table and then issues a SELECT statement against it, followed by an UPDATE or a DELETE, the rules of read consistency state that the SELECT statement cannot see the effects of the UPDATE or DELETE. To make that possible, the database needs that undo.

So a modification of a global temporary table needs to generate undo, and the undo tablespace must be protected by redo.

From 12c onwards, 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. If you have large batch operations that utilize global temporary tables, you may well discover that the amount of redo you generate decreases by a large amount. Furthermore, you’ll be generating less undo in your undo tablespace. And that means you’ll be able to support a longer undo_retention time with a smaller undo tablespace.

Now as we understood the benefits behind having separate undo for changes in global temporary tables, lets dig in to see how we could use this feature:-

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:-

<pre>SQL> alter session set temp_undo_enabled = false;

Session altered.

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

Statistics
———————————————————————————
…
<strong>     566304  redo size</strong>
…

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

Statistics
————————————————————————————
… 
<strong>    8243680  redo size</strong>
…

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).

Now if I enable temporary undo, :-

<pre>SQL> alter session set temp_undo_enabled = true;

Session altered.

SQL> insert into gtt select * from all_objects;

87310 rows created.

Statistics
———————————————————————————————
…
<strong>        280  redo size</strong>
…

SQL> update gtt set object_name = lower(object_name);
87310 rows updated.
Statistics
———————————————————————————————
…
<strong>          0  redo size</strong>
…

the redo is either trivial or nonexistent.

Hence you can see that we can have significant improvements using this feature especially in case of UPDATEs and DELETEs.

Hope this explains the New feature of Global Temporary Table Undo in 12c, in case any further clarification is required please comment below and I’ll try to clarify it.