Flashback的原创用法

FLASHBACK TABLE

 

SQL> select * from aaa;

 

         AA BB

----------- ------------

          1 we

          2 sd

          3 xc

 

SQL> drop table aaa;

 

Table dropped

 

SQL> select * from aaa;

 

select * from aaa

 

ORA-00942: table or view does not exist

 

SQL> flashback table aaa to before drop;

 

Done

 

SQL> select * from aaa;

 

         AA BB

----------- ------------

          1 we

          2 sd

          3 xc

 

 

FLASHBACK ROWS

 

以下是根据scn号来实现flashback query的方式：

SQL> select * from aaa;

 

         AA BB

----------- ------------

          1 34ewr

          1 22

          1 2rr

          2 er

 

SQL> delete from aaa where aa=1;

 

3 rows deleted

 

SQL> commit;

 

Commit complete

 

SQL> select * from aaa;

 

         AA BB

----------- ------------

          2 er

 

SQL> select dbms_flashback.get_system_change_number from dual;

 

GET_SYSTEM_CHANGE_NUMBER

------------------------

          10692893748725

 

SQL> select * from aaa as of scn 10692893748723;

 

         AA BB

----------- ------------

          2 er

这个scn号可以根据

select sequence#,first_change#,next_change#,to_char(first_time,'YYYYMMDD HH24:MI:SS')  FROM v$log_history;

来做大致的时间段 参考判断；删除时间不在历史视图内的话，请用

select sequence#,first_change#,next_change#,to_char(first_time,'YYYYMMDD HH24:MI:SS')  FROM v$log;

此处有一点需要注意，在sqlplus格式下，first_change#等无法全部显示，查询时会导致ORA-08181: specified number is not a valid system change number错误，需要先column format处理为足够长度。

 

SQL> select * from aaa as of scn 10692893748623;

 

         AA BB

----------- ------------

          1 34ewr

          1 22

          1 2rr

          2 er

 

SQL>

 

 

另一种用时间点来flashback query的方式：

SQL> set time on

PM 02:21:45 SQL> select * from aaa;

 

         AA BB

----------- ------------

          1 e

         23 er

          3 5f

          4 fg

          2 er

 

 

PM 02:21:50 SQL> delete from aaa;

 

5 rows deleted

 

PM 02:25:26 SQL> commit;

 

Commit complete

 

PM 02:28:47 SQL> select dbms_flashback.get_system_change_number from dual;

 

GET_SYSTEM_CHANGE_NUMBER

------------------------

          10692893767326

 

 

PM 02:30:01 SQL> select * from aaa as of scn 10692893766000;

 

         AA BB

----------- ------------

          2 er

 

PM 02:30:05 SQL> select * from aaa as of scn 10692893766600;

 

         AA BB

----------- ------------

          1 e

         23 er

          3 5f

          4 fg

          2 er

 

PM 02:30:11 SQL> exec  dbms_flashback.enable_at_time(sysdate-10/1440);

 

PL/SQL procedure successfully completed

 

 

PM 02:33:05 SQL> select * from aaa;

 

         AA BB

----------- ------------

          1 e

         23 er

          3 5f

          4 fg

          2 er

 

 

select * from sys.smon_scn_time 查询数据库scn信息

 

 

ORA-08182: operation not supported while in Flashback mode

 

 

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as crystal

 

SQL> select * from salgrade;

 

     GRADE      LOSAL      HISAL

---------- ---------- ----------

         1        700       1200

         2       1201       1400

         3       1401       2000

         4       2001       3000

         5       3001       9999

 

SQL> insert into salgrade values(6,10000,20000);

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select * from salgrade;

 

     GRADE      LOSAL      HISAL

---------- ---------- ----------

         1        700       1200

         2       1201       1400

         3       1401       2000

         4       2001       3000

         5       3001       9999

         6      10000      20000

 

6 rows selected

 

SQL> exec  dbms_flashback.enable_at_time(sysdate-2/1440);

 

PL/SQL procedure successfully completed

 

SQL> select * from salgrade;

 

     GRADE      LOSAL      HISAL

---------- ---------- ----------

         1        700       1200

         2       1201       1400

         3       1401       2000

         4       2001       3000

         5       3001       9999

         6      10000      20000

 

6 rows selected

 

SQL> exec  dbms_flashback.enable_at_time(sysdate-1/1440);

 

begin dbms_flashback.enable_at_time(sysdate-1/1440); end;

 

ORA-08184: attempting to re-enable Flashback while in Flashback mode

ORA-06512: at "SYS.DBMS_FLASHBACK", line 3

ORA-06512: at line 2

 

SQL> select dbms_flashback.get_system_change_number from dual;

 

GET_SYSTEM_CHANGE_NUMBER

------------------------

                 1946263

 

SQL> select * from salgrade as of scn 1946000;

 

     GRADE      LOSAL      HISAL

---------- ---------- ----------

         1        700       1200

         2       1201       1400

         3       1401       2000

         4       2001       3000

         5       3001       9999

         6      10000      20000

 

6 rows selected

 

SQL> select * from salgrade as of scn 1945700;

 

select * from salgrade as of scn 1945700

 

ORA-01466: unable to read data - table definition has changed

 

SQL> select * from salgrade as of scn 1945800;

 

     GRADE      LOSAL      HISAL

---------- ---------- ----------

         1        700       1200

         2       1201       1400

         3       1401       2000

         4       2001       3000

         5       3001       9999

 

SQL> flashback table salgrade to scn 1945800;

 

flashback table salgrade to scn 1945800

 

ORA-08189: cannot flashback the table because row movement is not enabled

 

SQL> alter table salgrade enable row movement;

 

alter table salgrade enable row movement

 

ORA-08182: operation not supported while in Flashback mode

 

SQL> exec  dbms_flashback.disable;

 

PL/SQL procedure successfully completed

 

SQL> alter table salgrade enable row movement;

 

Table altered

 

SQL> flashback table salgrade to scn 1945800;

 

Done

 

SQL> select * from salgrade;

 

     GRADE      LOSAL      HISAL

---------- ---------- ----------

         1        700       1200

         2       1201       1400

         3       1401       2000

         4       2001       3000

         5       3001       9999

 

SQL>