闪回查询——基于事物级别的闪回

[oracle@feegle ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 6 10:11:40 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

--使用管理员登录，创建测试数据
SQL> conn /as sysdba
Connected.
SQL> create tablespace ts_flashback datafile '/u02/oradata/data/orcl/ts_flashback01.dbf' size 10m autoextend on maxsize 1g;

Tablespace created.

SQL> create user u_flashback identified by "123456" default tablespace ts_flashback;

User created.

SQL> grant connect,resource to u_flashback;

Grant succeeded.

SQL> create table u_flashback.tb_flashback as select * from dba_objects;

Table created.

--执行闪回操作，需要授予用户u_flashback可execute权限
SQL> grant execute on sys.dbms_flashback to u_flashback;

Grant succeeded.

--切换到u_flashback用户登录
SQL> disc;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn u_flashback@orcl
Enter password:
Connected.

--记录后面执行闪回的目标时间
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012-07-06 14:30:09

SQL> select count(*) from tb_flashback;

  COUNT(*)
----------
     50286

--删除tb_flashback表的数据
SQL> delete from tb_flashback;

50286 rows deleted.

SQL> commit;

Commit complete.

--查询tb_flashback表的当前数据记录数
SQL> select count(*) from tb_flashback;

  COUNT(*)
----------
         0

--2012-07-06 14:30:09距离2012-07-06 14:35:10大约6分钟
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012-07-06 14:35:10

--执行闪回操作，即闪回到6分钟前
SQL> execute dbms_flashback.enable_at_time(sysdate-6/1440);

PL/SQL procedure successfully completed.

--再次查询tb_flashback表的当前数据记录数，发现tb_flashback表的记录数与删除前的记录数相同
SQL> select count(*) from tb_flashback;

  COUNT(*)
----------
     50286