undo段中undo_retention参数,单位秒,表示提交后在undo段中保留时间

SQL> conn / as sysdba
Connected.
SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

SQL> alter system set undo_retention = 7200;——两个小时

System altered.

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     7200

保留两小时，其实不能保证两个小时内的提交的DML一定能恢复，因为undo tablespace中没空间时会占用undo_retention要求保留的磁盘空间，这保留的空间会被刷掉。
这参数是针对整个数据库的，设太大会占用磁盘空间（undo表空间）
提示：安全和效率是矛盾的，要折衷。
例子：
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

SQL> conn scott/tiger
Connected.

SQL> update dept set loc='bj';

4 rows updated.

查看刚刚的DML的相关信息：
SQL> select versions_xid,deptno,dname,loc
  2  from dept
  3  versions between scn  minvalue and maxvalue
  4  where deptno=10;

VERSIONS_XID         DEPTNO DNAME          LOC
---------------- ---------- -------------- -------------
                         10 ACCOUNTING     NEW YORK

versions_xid为空，因为刚刚的DML没提交

SQL> commit;

Commit complete.

SQL> select versions_xid,deptno,dname,loc
  2  from dept
  3  versions between scn  minvalue and maxvalue
  4  where deptno=10;

VERSIONS_XID         DEPTNO DNAME          LOC
---------------- ---------- -------------- -------------
0A000300D0000000         10 ACCOUNTING     bj
                         10 ACCOUNTING     NEW YORK
提交后，VERSIONS_XID有值了
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

SQL> conn / as sysdba
Connected.
SQL> col operation format a10
SQL> col undo_sql format 99999;
SQL> set pagesize 100;


用flashback_transaction_query查看undo_sql和commit的DML操作
SQL> select undo_sql,operation
  2  from flashback_transaction_query
  3  where xid=hextoraw('0A000300D0000000');——DML操作的versions_xid

UNDO_SQL
--------------------------------------------------------------------------------
OPERATION
----------
update "SCOTT"."DEPT" set "LOC" = 'BOSTON' where ROWID = 'AAAMfKAAEAAAAAQAAD';
UPDATE

update "SCOTT"."DEPT" set "LOC" = 'CHICAGO' where ROWID = 'AAAMfKAAEAAAAAQAAC';
UPDATE

update "SCOTT"."DEPT" set "LOC" = 'DALLAS' where ROWID = 'AAAMfKAAEAAAAAQAAB';
UPDATE

update "SCOTT"."DEPT" set "LOC" = 'NEW YORK' where ROWID = 'AAAMfKAAEAAAAAQAAA';
UPDATE
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝



利用SCN号进行闪回：

SQL> select START_SCN,operation ——利用刚刚的versions_xid查对应的SCN
  2  from flashback_transaction_query
  3  where xid=hextoraw('0A000300D0000000');——DML操作的versions_xid

 START_SCN OPERATION
---------- ----------
    509098 UPDATE
    509098 UPDATE
    509098 UPDATE
    509098 UPDATE


SQL> flashback table scott.dept to SCN 509098;
flashback table scott.dept to SCN 509098
                      *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

报错是正常的!要先打开行移动功能（Oracle默认是关闭的）
SQL> alter table scott.dept enable row movement;

Table altered.

SQL> flashback table scott.dept to SCN 509098;

Flashback complete.

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝


利用时间进行闪回，原理：把SCN号做个转换而已，改一下语句语法
SQL> conn scott/tiger
Connected.
SQL> ho date
Wed Nov 30 12:49:11 CST 2011——记下修改前的时间，方便恢复

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update dept set loc = 'shenzhen';

4 rows updated.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     shenzhen
        20 RESEARCH       shenzhen
        30 SALES          shenzhen
        40 OPERATIONS     shenzhen

SQL> flashback table scott.dept
  2  to timestamp  to_timestamp('2011-11-30 12:49:11','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> select * from dept;——成功回到那个修改前的时间点

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝


实际：进行大量的DML操作，很难知道误操作的DML时间，所以多退些时间。但是会丢失不少数据，所以用SCN定位个更保险。要丢数据，都丢少一点比较好。