Investigating Redo - Oracle Database Transactions and Locking Revealed - page 141

Starting with 12c
As you saw in the previous section, when issuing INSERT , UPDATE , and DELETE statements in a temporary table, the
undo for those changes is recorded in the undo tablespace, which in turn will generate redo. With the advent of
Oracle 12 c , you can instruct Oracle to store the undo for a temporary table in a temporary tablespace via the
TEMP_UNDO_ENABLED parameter. When blocks are modified in a temporary tablespace, no redo is generated. Therefore,
when TEMP_UNDO_ENABLED is set to TRUE , any DML issued against a temporary table will generate little or no redo.

■ By default, TEMP_UNDO_ENABLED is set to FALSE . So unless otherwise configured, temporary tables will generate
the same amount of redo in 12 c as in prior releases.

Note

The TEMP_UNDO_ENABLED parameter can be set at the session or system level. Here's an example of setting it to
TRUE at the session level:

EODA@ORA12CR1> alter session set temp_undo_enabled=true;

Once enabled for a session, any modifications to data in a temporary table in that session will have a subsequent
undo logged to the temporary tablespace. Any modifications to permanent tables will still have undo logged to the
undo tablespace. To see the impact of this, I'll rerun the exact same code (from the “Prior to 12c” section) that displays
the amount of redo generated when issuing transactions against a permanent table and a temporary table—with the
only addition being that TEMP_UNDO_ENABLED is set to TRUE . Here is the output:

3,312,148 bytes of redo generated for "insert into perm"...
376 bytes of redo generated for "insert into temp"...

2,203,788 bytes of redo generated for "update perm set x = 2"...
376 bytes of redo generated for "update temp set x = 2"...

3,243,412 bytes of redo generated for "delete from perm"...
376 bytes of redo generated for "delete from temp"...

The results are dramatic: a trivial amount of redo is generated by the INSERT , UPDATE , and DELETE statements in a
temporary table. For environments where you perform large batch operations that transact against temporary tables,
you can expect to see a significant reduction in the amount of redo generated.

■ You may be wondering why there were 376 bytes of redo generated in the prior example's output. as processes
consume space within the database, Oracle does some internal housekeeping. these changes are recorded in the data
dictionary, which in turn generates some redo and undo.

Note