闪回查询与闪回表(Flashback Query、Flashback Table)

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>