oracle查询flashback,Oracle Flashback 闪回查询功能

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