ORACLE 11G Flashback Versions Query

http://blog.csdn.net/mengxiang209/article/details/6906002
從oracle 10g開始，Flashback Technologies有了很大的改進，其中的Flashback Versions Query技術可以讓你輕鬆檢視到兩個時間點或scn點之間的同一資料的變動情況(必須是在flashback範圍以內)。例如下面的語句可以輕鬆檢視 t_dept表中09:00:00到09:16:00之間的資料變化情況：

SQL> SELECT * FROM mis.t_sys_dept
  2  VERSIONS BETWEEN TIMESTAMP
  3  to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND
  4  to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')
  5  WHERE DEPTid=5522;

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

deptid     deptcode     deptname   deptlevel terminated parent   flag     manager       short    depttype    sort     u8code
5522        101004       投資公司        2               0                   1          1          5697                           0                   7         TZ
5522        101004       投資公司        2               0                   1          0          5697                           0                   7         TZ

2 rows selected

 

表不能使用別名：如果在表名後面加上別名，則會報錯。

SQL> SELECT * FROM mis.t_sys_dept d
  2  VERSIONS BETWEEN TIMESTAMP
  3  to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND
  4  to_timestamp('2011-10-26 09:16:00','yyyy-mm-dd hh24:mi:ss')
  5  WHERE D.DEPTid=5522;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
VERSIONS BETWEEN TIMESTAMP
*
ERROR AT line 2:
ORA-00933: SQL command NOT properly ended

 

偽列：

如果使用偽列，則不能使用*來統配表中所有的欄位：

 

SQL> SELECT versions_operation,* FROM oa.t_dept
  2  VERSIONS BETWEEN TIMESTAMP
  3  to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND
  4  to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')
  5  WHERE DEPTid=5522;
SELECT versions_operation,* FROM oa.t_dept
                          *
ERROR at line 1:
ORA-00936: missing expression

SQL> SELECT *,versions_operation FROM oa.t_dept
  2  VERSIONS BETWEEN TIMESTAMP
  3  to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND
  4  to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')
  5  WHERE DEPTid=5522;
SELECT *,versions_operation FROM oa.t_dept
        *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

 

使用偽列和欄位名：

SQL> SELECT deptid,deptname,versions_operation FROM oa.t_dept
  2  VERSIONS BETWEEN TIMESTAMP
  3  to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND
  4  to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')
  5  WHERE DEPTid=5522;

 DEPTID DEPTNAME    V
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -
      5522 投資公司     U
      5522 投資公司     U
      5522 投資公司

 

如果要使用*來統配表中所有的欄位，則必須在*前加上表名：

SQL> SELECT oa.t_dept.*,versions_operation FROM oa.t_dept
  2  VERSIONS BETWEEN TIMESTAMP
  3  to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND
  4  to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')
  5  WHERE DEPTid=5522;

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

deptid     deptcode     deptname   deptlevel terminated parent   flag     manager       short    depttype    sort     u8code
5522        101004       投資公司        2               0                   1          1          5697                           0                   7         TZ
5522        101004       投資公司        2               0                   1          0          5697                           0                   7         TZ

2 rows selected

 

 

不知道為什麼在Flashback Versions Query中不能用表的別名，難道是oracle的BUG？

 

注：關於Flashback Pseudocolumns介紹

 

VERSIONS_STARTSCN
        Starting SCN when the row was first created. This identifies the SCN when the data first took on the values displayed in the row version.If NULL, the row version was created before the lower time bound of the query BETWEEN clause.

VERSIONS_STARTTIME
        Starting TIMESTAMP when the row version was first created. This identifies the time when the data first took on the values displayed in the row version. If NULL, the row version was created before the lower time bound of the query BETWEEN clause.

VERSIONS_ENDSCN
        Ending SCN when the row version expired. This identifies the row expiration SCN. If NULL, then either the row version is still current or
the row corresponds to a DELETE operation.

VERSIONS_ENDTIME
        Ending TIMESTAMP when the row version expired. This identifies the row expiration time. If NULL, then either the row version is still current
or the row corresponds to a DELETE operation.

VERSIONS_XID
        Identifier of the transaction that created the row version.

VERSIONS_OPERATION

        This is the operation performed by the transaction that modified the data. The values are I for insertion, D for deletion, or U for update.