Flashback Query闪回查询
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秒；
undo_tablespace：指定undo表空间
注意：如果undo表空间为auto管理方式，那么当undo表空间中的空间不足的时候，还是会覆盖保留区内的undo数据，也就说这种情况下
不能保证undo数据保留900秒，900秒内不一定能恢复dml操作。特殊情况下可以用下面命令长期保留undo数据，但为影响undo空间和dml操作
SQL>alter tablespace undotbs1 retention guarantee;
示例说明：
将test01表在1分钟内删除的数据插入test01_delete表中或创建表test01_delete
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);
--也可以基于scn闪回
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; --查看scn
GET_SYSTEM_CHANGE_NUMBER
------------------------
1054345
SQL> grant flashback any table to scott; --赋予scott flashback table权限
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'; --基于scn闪回表
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>