[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
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