undo GUARANTEE

1.当 undo 为  autoextned on 时.   那就不用讲了  保存时间为  undo_retention  的时间.
2.当 undo 为  autoextend off 时,既是 fixed undo 的情况.
  undo 为  ALTER TABLESPACE undotest RETENTION GUARANTEE; 时.undo_retention   的时间内, undo 内容不会被覆盖. 其它 dml 会报错.


3.实验内容
   3.1 搭建表数据
       create table system.test as select * from dba_objects;
       insert into system.test select * from system.test;
       create table system.test1 as select * from system.test;


       UNDOTEST                               100      40.19      59.81   40.19


   3.2 guarantee情况
       alter system set undo_retention=9000 scope=spfile;
       alter tablespace undotest retention guarantee;


       session 1
       SQL>  update system.test set owner = 'a';
       346736 rows updated.
       SQL> commit;
       Commit complete.
      
       session 2
       SQL> update system.test1 set owner  = 'bb';
       update system.test1 set owner  = 'bb'
              *
       ERROR at line 1:
       ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTEST'
       此时   undo 明显不可以进行覆盖
   3.3 noguarantee情况
       
       alter system set undo_retention=9000 scope=spfile;
       alter tablespace undotest retention noguarantee;


       session 1
       SQL> update system.test set owner = 'xxx';
       346736 rows updated.
       SQL> commit;
       Commit complete.
       
       session 2
       
       SQL> insert into system.test select * from system.test;
346736 rows created.
SQL> commit;
Commit complete.
SQL> update system.test set owner = 'kkk';
693472 rows updated.
SQL> commit;
Commit complete.
        把undo 占满


        这时  undo 肯定被覆盖了，我们验证一下.
SQL> select owner from system.test as of timestamp to_timestamp('2017-09-07 00:04:12', 'yyyy-mm-dd hh24:mi:ss');
select owner from system.test as of timestamp to_timestamp('2017-09-07 00:04:12', 'yyyy-mm-dd hh24:mi:ss')
                         *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 2 with name
"_SYSSMU2_3883785582$" too small


       

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7569309/viewspace-2144640/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7569309/viewspace-2144640/