Question: How
do I use the 12c temp_undo_enabled parameter?
Answer:
Oracle 12c introduced the concept of temporary UNDO
logs, and the temp_undo_enabled=true (default
temp_undo_enabled=false) is an Oracle 12c and beyond
parameter that will enable UNDO to use the TEMP UNDO
tablespace.
rior to Oracle12c, Oracle transactions used UNDO for
temporary tables (WITH Clause materializations, global
temporary tables) within the standard UNDO tablespace.
Now, you can specify "alter session
set temp_undo_enabled=true" to force the
UNDO to be managed within the TEMP
tablespace instead of within the UNDO
tablespace.
This reduced the content of "regular"
UNDO allowing for faster flashback
operations.
Oracle has also allowed "private
optimizer statistics" for global temporary
tables, instead of the Oracle 11g method in
which everybody shared a single set of
statistics.
The Oracle docs says that you should set
temp_undo_enabled=true
when you are using temporary objects (global temporary
tables, subquery factoring, the WITH clause):
"If database applications make use of temporary objects
(using global temporary tables or temporary table
transformations), it is advisable to set this parameter's
value to true."
Usage statistics on TEMP UNDO are available in the 12c view
v$tempundostat.
For example, we can change this with the alter session
command
alter session set
temp_undo_enabled=true;
create global temporary table xxx . . . .
