In regard to undo generation, private temporary tables behave similar to global temporary tables. We can see that by running the same tests from the prior section to measure the amount of undo generated when using a temporary table. First, let’s create the permanent and the private temporary tables:

$ sqlplus eoda/foo@PDB1
SQL> alter session set temp_undo_enabled=false; SQL> create table perm
( x char(2000) ,y char(2000) ,z char(2000) );

The bottom line is that when you use either global temporary tables or private temporary tables, if you want to minimize undo generation, set TEMP_UNDO_ENABLED to TRUE.

Investigating Undo

The Oracle database creates and manages information used to roll back (or undo) changes to the database. The most obvious use of undo is when you issue a ROLLBACK statement to undo changes to data that you don’t want committed.

Here is the complete list of uses of undo:
•\ Roll back transactions via the ROLLBACK statement
•\ Enable read consistency
•\ Recover the database
•\ Analyze data as of a prior point in time using Oracle Flashback Query
•\ Recover from logical corruptions using Oracle Flashback features

We’ve already discussed several of the prior undo segment topics. We’ve seen how they are used during recovery, how they interact with the redo logs, and how they are used for consistent reads and nonblocking reads of data. In this section, we’ll look at the most frequently raised issues with undo segments.

The bulk of our time will be spent on the infamous ORA-01555: snapshot too old error, as this single issue causes more confusion than any other topic in the entire set of database topics.
Before we do this, however, we’ll investigate one other undo-related issue: the question of what type of DML operation generates the most and least undo (you might already be able to answer that yourself, given the examples in the preceding section with temporary tables).