Temporary Undo 的概念

我们知道，临时表三大好处：
会话独立；数据自动清理；没有REDO

看 Persistent Table （蓝色部分），对于普通表来说，既会产生 redo，也会产生 unod，同时， undo 块的变化也会产生 redo。

看 Temporary Table （棕色部分），对于临时表来说：

REDO： no redo entries are directly generated for changes on temporary tables，这也是临时表比普通表快的原因。

UNDO： 临时表会产生 undo 数据以支持回滚。
如果是12c之前的版本或者没有启用 Temporary Undo，则临时表产生的 undo 数据(Permanent Undo)存放在 Undo 表空间中，继而产生 UNDO 相关的 REDO 日志。
如果启用了 Temporary Undo，则临时表产生的 undo 数据(Temporary Undo)存放在临时表空间中，而不会产生 UNDO 相关的 REDO 日志。

Temporary Undo 的好处

• Temporary undo reduces the amount of undo stored in the undo tablespaces.
• Temporary undo reduces the size of the redo log.
• Temporary undo enables DML operations on temporary Temporary undo enables DML operations on temporary tables in a physical standby database with the Oracle Active Data Guard option. However, DDL operations that create temporary tables must be issued on the primary database.

启用 Temporary UNDO：

SQL> ALTER session SET temp_undo_enabled = true;
SQL> ALTER system SET temp_undo_enabled = true;
Temporary undo mode is selected when a session first uses a temporary object.

巩固测试题目

You use multiple temporary tables frequently in your database. Which two are benefits of configuring temporary undo? (Choose two.)
A. Performance improves because less redo is written to the redo log.
B. Temporary undo reduces the amount of undo stored in undo tablespaces.
C. Performance improves because data manipulation language (DML) operations performed on temporary tables do not use the buffer cache.
D. Performance improves because no redo and undo are generated for the temporary tables.

答案：AB