Flashback Query、Flashback Table(快速閃回查詢、快速閃回表)

 flashback query是基於undo資料表空間的閃回，與之相關的參數如下：

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                string      AUTO        
undo_retention                       integer     900
undo_tablespace                     string      UNDOTBS1

undo_management：undo管理方式分自動auto和手動manul兩種，預設為auto。

undo_retention：此參數表示，如果undo資料表空間中的空間充足，那麼undo資料至少為保留900秒；

 

 

 

SQL> insert into test01_delete select * from test01 as of timestamp systimestamp - interval '1' minute

          where id not in(select id from test01);

SQL>create table test01_delete as select * from test01 as of timestamp systimestamp - interval '1' minute

         where id not in(select  id from test01);

 

 Flashback Table閃回表的使用

Flashback Table和Flashback Query一樣是基於undo的閃回。 閃回表時使用者必須擁有flashback table許可權和表row movement功能;

樣本：

 [oracle@b1 ~]$ export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

SQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') sys_date from dual;  

SYS_DATE
-------------------
2013-10-06 18:11:39

SQL>  select dbms_flashback.get_system_change_number from dual;  

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1054345

SQL> grant flashback any table to scott;    

Grant succeeded.

SQL> conn scott/xyc
Connected.

SQL> select * from xyc_t1;

     EMPNO ENAME                JOB                       MGR HIREDATE                   SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
      7698 BLAKE                MANAGER                  7839 1981-05-01 00:00:00       2850                    30
      7902 FORD                 ANALYST                  7566 1981-12-03 00:00:00       8888                    20

SQL> delete from xyc_t1;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> flashback table xyc_t1 to scn '1054345';
flashback table xyc_t1 to scn '1054345'
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled  -必須開啟行移動功能

SQL> alter table xyc_t1 enable row movement;  -開啟行移動功能

Table altered.

SQL> flashback table xyc_t1 to scn '1054345';  

Flashback complete.

SQL> select * from xyc_t1;

     EMPNO ENAME                JOB                       MGR HIREDATE                   SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
      7698 BLAKE                MANAGER                  7839 1981-05-01 00:00:00       2850                    30
      7902 FORD                 ANALYST                  7566 1981-12-03 00:00:00       8888                    20

SQL> flashback table xyc_t1 to timestamp to_timestamp('2013-10-06 18:10:51','YYYY-MM-DD HH24:MI:SS'); 

Flashback complete.

SQL> select * from xyc_t1;

     EMPNO ENAME                JOB                       MGR HIREDATE                   SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
      7698 BLAKE                MANAGER                  7839 1981-05-01 00:00:00       2850                    30
      7902 FORD                 ANALYST                  7566 1981-12-03 00:00:00       8888                    20

SQL>