5、临时undo在12c R1中，为了缩减UNDO段的使用，同时减少REDO和归档的数据量，这个特性将对于临时表的UNDO信息分离出去，独立存储在临时表空间中，这就减少了对于UNDO段的使用。由于临时表的UNDO信息通常用于读一致性和事务回滚，在事务完成之后，无需进行恢复，所以也就不必永久保存。这个特性完全无损Oracle的事务一致性，但是却得到了空间缩减的好处。这个特性将允许在ADG中对临时表进行DML操作。

--在系统级或者会话级启用这个特性的方法：
ALTER SYSTEM/SESSION SET TEMP_UNDO_ENABLED=true；
启用之后，对于临时表的UNDO信息，将自动存储在临时表空间中。
以下所列的字典视图是用来查看或查询临时undo数据相关统计信息的：
　　V$TEMPUNDOSTAT
　　DBA_HIST_UNDOSTAT
　　V$UNDOSTAT

--示例：
SQL> create global temporary table test2 as select * from dba_objects where 1=2;

Table created.

SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');

NAME VALUE
---------------------------------------------------------------- ----------
redo entries 47921
redo size 41808456

SQL> insert into test2 select * from dba_objects;

90775 rows created.

SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');

NAME VALUE
---------------------------------------------------------------- ----------
redo entries 50753
redo size 42408756

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@lfdz1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jun 21 15:32:21 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=LLFPDB;

Session altered.

SQL> alter session set temp_undo_enabled=true;

Session altered.

SQL> show parameter temp_undo_enabled;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean TRUE

SQL> insert into test2 select * from dba_objects;

90775 rows created.

SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');

NAME VALUE
---------------------------------------------------------------- ----------
redo entries 1
redo size 280

SQL> insert into test2 select * from dba_objects;

90775 rows created.

SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');

NAME VALUE
---------------------------------------------------------------- ----------
redo entries 1
redo size 280

ITPub博客

12c Oracle新特性5

最新文章