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.

Note By default, TEMP_UNDO_ENABLED is set to FALSE.

To fully understand the impact of TEMP_UNDO_ENABLED on redo generation, let’s first look at the behavior when TEMP_UNDO_ENABLED is set to FALSE. I set up a small test to demonstrate the amount of redo generated while working with temporary tables, an indication therefore of the amount of undo generated for temporary tables, since only the undo is logged for them.
To demonstrate, I’ll take identically configured permanent and temporary tables and then perform the same operations on each, measuring the amount of redo generated each time. The tables I’ll use are as follows:

$ sqlplus eoda/foo@PDB1
SQL> create table perm( x char(2000) ,y char(2000) ,z char(2000) );Table created.

As you can see

•\ The INSERT into the “real” table generated a lot of redo, while almost no redo was generated for the temporary table. This makes sense— there is very little undo data generated for INSERTs, and only undo data is logged for temporary tables.

•\ The UPDATE of the real table generated about twice the amount of redo as the temporary table. Again, this makes sense. About half of that UPDATE, the “before image,” had to be saved. The “after image”(redo) for the temporary table did not have to be saved.

•\ The DELETEs each took about the same amount of redo space. This makes sense, because the undo for a DELETE is big, but the redo for the modified blocks is very small. Hence, a DELETE against a temporary table takes place very much in the same fashion as a DELETE against a permanent table.

Therefore, the following generalizations can be made regarding DML activity on temporary tables:

•\ An INSERT will generate little to no undo/redo activity.
•\ An UPDATE will generate about half the redo as with a permanent table.
•\ A DELETE will generate the same amount of redo as with a permanent table.

Now, I’ll run the prior tests with the TEMP_UNDO_ENABLED set to TRUE. 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:

SQL> 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 run some code 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.

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

It’s worth noting that in an Oracle Active Data Guard configuration, you can issue DML statements directly on a temporary table that exists in a standby database. We can view the amount of redo generated for a temporary table in a standby database by running the same code again against a standby database. The only difference is the statements issuing transactions against permanent tables must be removed (because you cannot issue DML on a permanent table in a standby database). Here is the output showing that 0 bytes of redo are generated:

0 bytes of redo generated for “insert into temp”…
0 bytes of redo generated for “update temp set x = 2″…
0 bytes of redo generated for “delete from temp”…

Note There’s no need to set TEMP_UNDO_ENABLED in the standby database. This is because temporary undo is always enabled in an Oracle Active Data Guard standby database.

Global temporary tables are often used for reporting purposes—like generating and storing intermediate query results. Oracle Active Data Guard is often used to offload reporting applications to the standby database. Couple global temporary tables with Oracle Active Data Guard, and you have a more powerful tool to address your reporting requirements.