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>