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