【oracle】flashback query

scs653298

　　今天无意间有个同事问 SELECT employee_id,salary FROM employees AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL'10' minute).
　　表达的意思。看到 as of ，之前还真没见过，遂查之。
　　原来这句话是想查 employees 十分钟之前的数据。
　　随后就引出了oracle的 flashback 技术。
　　本文通过实验说明其中的 flashback query 部分。
　　查看数据库版本：
　　SQL> select * from v$version;
　　BANNER
　　----------------------------------------------------------------

　　Oracle Database 10g Enterprise Edition>
　　PL/SQL>　　CORE    10.2.0.3.0    Production
　　TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
　　NLSRTL Version 10.2.0.3.0 - Production
　　A表为实验数据
　　SQL> select * from a;
　　NUM NAME
　　---------- ----
　　12 d
　　12 d
　　12 d
　　12 d
　　12 d
　　12 d
　　6 rows selected
　　查看当前 SCN：
　　SQL> select dbms_flashback.get_system_change_number from dual;
　　GET_SYSTEM_CHANGE_NUMBER
　　------------------------
　　11274291850925
　　删除A表数据：
　　SQL> delete from a;
　　6 rows deleted
　　SQL> commit;
　　Commit complete
　　A表为空
　　SQL> select * from a;
　　NUM NAME
　　---------- ----
　　恢复到删除前的那个点
　　SQL> flashback table a to scn 11274291850925;
　　Done
　　查看A表中数据，已经恢复。
　　SQL> select * from a;
　　NUM NAME
　　---------- ----
　　12 d
　　12 d
　　12 d
　　12 d
　　12 d
　　12 d
　　6 rows selected
　　而在实际中，开发人员几乎不会去记录DML操作前的SCN。这时候就用另外的方法。
　　B表为测试表。
　　SQL> create table b as select * from a;
　　Table created
　　SQL> select * from b;
　　NUM NAME
　　---------- ----
　　12 d
　　12 d
　　12 d
　　12 d
　　12 d
　　12 d
　　6 rows selected
　　删除并提交
　　SQL> delete from b;
　　6 rows deleted
　　SQL> commit;
　　Commit complete
　　从flashback_transaction_query 查询我们需要的信息
　　SQL> select * from flashback_transaction_query where table_name='B';
　　XID               START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER  UNDO_CHANGE# OPERATION  TABLE_NAME TABLE_OWNERROW_ID              UNDO_SQL
　　---------------- ---------- --------------- ---------- ---------------- ----------- ------------ ---------- ----------------------------------------- ---------------------------------------------------------
　　02001900EC5B0000 1127429185 2012/3/19 21:26 1127429185 2012/3/19 21:27: SCOTT                  1 DELETE     B          SCOTT      AAAOkQAAGAABBTEAAF  insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　02001900EC5B0000 1127429185 2012/3/19 21:26 1127429185 2012/3/19 21:27: SCOTT                  2 DELETE     B          SCOTT      AAAOkQAAGAABBTEAAE  insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　02001900EC5B0000 1127429185 2012/3/19 21:26 1127429185 2012/3/19 21:27: SCOTT                  3 DELETE     B          SCOTT      AAAOkQAAGAABBTEAAD  insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　02001900EC5B0000 1127429185 2012/3/19 21:26 1127429185 2012/3/19 21:27: SCOTT                  4 DELETE     B          SCOTT      AAAOkQAAGAABBTEAAC  insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　02001900EC5B0000 1127429185 2012/3/19 21:26 1127429185 2012/3/19 21:27: SCOTT                  5 DELETE     B          SCOTT      AAAOkQAAGAABBTEAAB  insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　02001900EC5B0000 1127429185 2012/3/19 21:26 1127429185 2012/3/19 21:27: SCOTT                  6 DELETE     B          SCOTT      AAAOkQAAGAABBTEAAA  insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　6 rows selected
　　在最后可以看到 undo_sql列。利用此列的内容，就可以恢复之前的数据。
　　insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　insert into "SCOTT"."B"("NUM","NAME") values ('12','d ');
　　SQL> commit;
　　Commit complete
　　查询B表
　　SQL> select * from b;
　　NUM NAME
　　---------- ----
　　12 d
　　12 d
　　12 d
　　12 d
　　12 d
　　12 d
　　6 rows selected