flashback version query不是Oracle的什麼新特性，但10g、11g都在9i基礎上做了功能增強，在某些場合可能會用到。下面利用樣本展示一下他的概念；
- Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- Connected as hr
-
- SQL> show user;
- User is "hr"
-
- SQL> show parameter undo
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- undo_management string AUTO
- undo_retention integer 9000
- undo_tablespace string UNDOTBS1
-
- SQL> truncate table t1;
-
- Table truncated
-
- SQL> select systimestamp from dual;
-
- SYSTIMESTAMP
- --------------------------------------------------------------------------------
- 31-DEC-11 02.58.26.560000 PM +08:00
-
- SQL> insert into t1 values(1,'jash');
-
- 1 row inserted
-
- SQL> commit;
-
- Commit complete
-
- SQL> update t1 set name ='bob' where id =1;
-
- 1 row updated
-
- SQL> commit;
-
- Commit complete
-
- SQL> select systimestamp from dual;
-
- SYSTIMESTAMP
- -----------------------------------------
- 31-DEC-11 02.59.04.318000 PM +08:00
-
- SQL>
- SQL> SELECT versions_startscn, versions_starttime,
- 2 versions_endscn, versions_endtime,
- 3 versions_xid, versions_operation,
- 4 id, name
- 5 FROM hr.t1
- 6 VERSIONS BETWEEN TIMESTAMP
- 7 TO_TIMESTAMP('2011-12-31 14:58:26', 'YYYY-MM-DD HH24:MI:SS')
- 8 AND TO_TIMESTAMP('2011-12-31 14:59:04', 'YYYY-MM-DD HH24:MI:SS');
-
|
查詢結果欄位標題有些長，我做了截斷，要不然整行顯示不下，請對照上面的查詢語句看欄位標題；
- SQL>
- SQL> insert into t1 values(2,'clark');
-
- 1 row inserted
- SQL> insert into t1 values(3,'pig');
-
- 1 row inserted
- SQL> insert into t1 values(4,'duck');
-
- 1 row inserted
-
- SQL> commit;
-
- Commit complete
-
- SQL>
- SQL> update t1 set name='kate' where id=2;
-
- 1 row updated
- SQL> update t1 set name='luna' where id=2;
-
- 1 row updated
-
- SQL> commit;
-
- Commit complete
-
- SQL> select systimestamp from dual;
-
- SYSTIMESTAMP
- --------------------------------------------------------------------------------
- 31-DEC-11 03.05.31.071000 PM +08:00
-
- SQL>
- SQL> SELECT versions_startscn, versions_starttime,
- 2 versions_endscn, versions_endtime,
- 3 versions_xid, versions_operation,
- 4 id, name
- 5 FROM hr.t1
- 6 VERSIONS BETWEEN TIMESTAMP
- 7 TO_TIMESTAMP('2011-12-31 14:58:26', 'YYYY-MM-DD HH24:MI:SS')
- 8 AND TO_TIMESTAMP('2011-12-31 15:05:31', 'YYYY-MM-DD HH24:MI:SS');
|
做一下簡要描述：
第六行：start_scn：3852006 end_scn：3852023 operation：I(insert) 表示插入values(1,'jash')
第五行：start_scn：3852023 operation：U(update) 表示更新'bob' id為1的記錄，所以第六行新插入的資料被更新了；
... ...
... ...
可以看到通過查詢可以找到所關注行的版本記錄，其中必須存在於undo復原段中，commit已提交的事務，呵呵；
最後看一下官方文檔的概述：
Oracle Flashback features use the Automatic Undo Management (AUM) system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions. For example, if a user runs an UPDATE statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data.Undo data is persistent and survives a database shutdown.
By using flashback features, you can use undo data to query past data or recover from logical damage. Besides using it in flashback features, Oracle Database uses undo data to perform these actions:
•Roll back active transactions
•Recover terminated transactions by using database or process recovery
•Provide read consistency for SQL queries
