今天无意间有个同事问 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 Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
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
