Temporary Undo in 12C

Download PDF of article now http://goo.gl/9FKCE8

Temporary tables are very important when it comes to staging some results and a big advantage is that no redo is generated for this tables, but undo for these tables go to undo tablespace and as we know whatever comes in undo a corresponding change vector goes to redo logs as well, so there is an indirect redo generated for temporary tables not a direct one. So what if the undo for these tables are not logged in redo logs, they just go to temporary tablespace thats it, Welcome to 12C. Because by default undo for temporary tables goes to undo tablespace because of which we have so much redo overhead and archive overhead in flash recovery area, So Oracle Database 12C has resolved this problem. Lets understand in more details again :=

As we know by default undo records for Global temporary tables are stored in the undo tablespace which is set by undo_tablespace paramater and are logged in the redo also which can be very expensive depending on the amount of changes which are done on the global temporary tables. TEMP_UNDO_ENABLED in 12C is a new initialization parameter which enables a session to have a temporary undo log. So if we enable this parameter, undo for global temporary tables will go in TEMPORARY tablespace, in contrast to the default which writes the changes to the UNDO tablespace. This can help improve performance and reduce UNDO and REDO for sure. This parameter can be set at the session level or system level scope. So this way undo changes to temporary objects goes to temporary undo log and changes to persistent objects goes to permanent undo log which is active undo tablespace set by undo_tabelspace.

With this not only the performance is improved but it reduces disk space for UNDO tablespaces, archivelogs, and backups. Moreover DML on global temporary tables in a physical standby database is also possible with this parameter if it is set to true. This parameter is only applicable for the primary database. For a standby database, this parameter is ignored because temporary undo is enabled by default on the standby database with the Oracle Active Data Guard option. However, data definition language (DDL) operations that create temporary tables must be issued on the primary database.

SQL> show parameter temp_undo_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
temp_undo_enabled                    boolean     FALSE


To enable temporary undo for a session, run the following SQL statement:

ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

To disable temporary undo for a session, run the following SQL statement:

ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;

To enable temporary undo for the system, run the following SQL statement:

ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

To disable temporary undo for the system, run the following SQL statement:

ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;

______________________________________________________

The following are the views which holds information about undo and temporary undo.

V$UNDOSTAT
V$TEMPUNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS
DBA_HIST_UNDOSTAT

Download PDF of article now http://goo.gl/9FKCE8