Oracle Flashback 闪回查询功能

来源：互联网发布：win8 java 编辑：程序博客网时间：2024/05/17 07:45

SELECT * FROM CT_CHA_ReadAmmeterEntry AS OF TIMESTAMP
TO_TIMESTAMP('2013-02-27 15:17:00','YYYY-MM-DD HH24:MI:SS')
where to_char(cfbizdate,'yyyyMMdd')='20130226'

and cfaddressno>='01000000' and cfaddressno<='01999999';

版本查看
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

创建test1表
SQL> create table test1
2 (id number,
3   name varchar2(20)
4 );

创建test2表
SQL> create table test2
2 (id number,
3   name varchar2(20)
4 );

在test1表中插入一条记录
SQL> insert into test1
2 values (1, 'jack');

1 row created.

SQL> commit;

Commit complete.

查看表情况
SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1                          TABLE
TEST2                          TABLE

查看闪回区表情况，没有记录。
SQL> select * from user_recyclebin;

no rows selected

删除test1表
SQL> drop table test1;

Table dropped.

查看表情况，test1表已经没有了
SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST2                          TABLE

查看闪回区，存在test1表
SQL> select object_name, original_name, droptime, createtime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME     DROPTIME            CREATETIME
------------------------------ ----------------- ------------------- -------------------
BIN$bweC0B3J2eLgQKjAZTgf0g==$0 TEST1             2009-07-19:12:32:36 2009-07-19:12:20:05

恢复TEST1表
SQL>flashback table test1 to before drop;

Flashback complete.

查看闪回区表情况，没有记录。
SQL> select object_name, original_name, droptime, createtime from recyclebin;

no rows selected

查看表情况，test1表已经恢复回来
SQL> select * from tab where tname like upper('test%');

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1                          TABLE
TEST2                          TABLE

表中记录也都在
SQL> select * from test1;

        ID NAME
---------- --------------------
         1 jack

在闪回区彻底删除TEST1表
SQL> drop table test1;

Table dropped.

SQL> select * from tab where tname like upper('test%');

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST2                          TABLE

SQL> select object_name, original_name, droptime, createtime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME            CREATETIME
------------------------------ -------------------------------- ------------------- -------------------
BIN$bweC0B3K2eLgQKjAZTgf0g==$0 TEST1                            2009-07-19:12:42:25 2009-07-19:12:20:05

在闪回区彻底删除表
SQL>purge table test1;

Table purged.

SQL> select object_name, original_name, droptime, createtime from recyclebin;

no rows selected

不进闪回区直接删除TEST2表
SQL> drop table test2 purge;

Table dropped.

查看表已经没有
SQL> select * from tab;

no rows selected

闪回区中也没有任何记录
SQL> select object_name, original_name, droptime, createtime from recyclebin;

no rows selected