flashback table(闪回表)——基于表级别的闪回

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

--查看表未删除前的块大小
SQL> select owner,bytes,blocks,extents,relative_fno from dba_segments where segment_name='TB_FLASHBACK';

OWNER                               BYTES     BLOCKS    EXTENTS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ------------
U_FLASHBACK                       6291456        768         21            6

--使用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> drop table tb_flashback;

Table dropped.

--从回收站recyclebin中查询表tb_flashback的object_name(注：从oracle10g开始，drop table并未真正删除表，只是将表标识为失效并重新命名，在recyclebin里仍可查到该表)
SQL> select object_name,original_name,type,createtime,droptime,dropscn from recyclebin where original_name='TB_FLASHBACK';

OBJECT_NAME                    ORIGINAL_NAME                    TYPE                      CREATETIME          DROPTIME               DROPSCN
------------------------------ -------------------------------- ------------------------- ------------------- ------------------- ----------
BIN$xCEsCr2ZifjgQKjAZO4Ukw==$0 TB_FLASHBACK                     TABLE                     2012-07-06:10:23:43 2012-07-06:10:39:23    1035253

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 /as sysdba
Connected.
--根据删除后的表名(object_name)查询表的数据块大小，可发现表并未真正删除
SQL> select owner,bytes,blocks,extents,relative_fno from dba_segments where segment_name='BIN$xCEsCr2ZifjgQKjAZO4Ukw==$0';

OWNER                               BYTES     BLOCKS    EXTENTS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ------------
U_FLASHBACK                       6291456        768         21            6

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 table_name from user_tables;

no rows selected

--闪回表
SQL> flashback table tb_flashback to before drop;

Flashback complete.

--查询，发现闪回表成功
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TB_FLASHBACK