ORACLE 11G Flashback Versions Query

最新推荐文章于 2024-06-07 18:00:00 发布

mengxiang209

最新推荐文章于 2024-06-07 18:00:00 发布

阅读量1.1k

点赞数

分类专栏： ORACLE New Features 文章标签： query oracle sql manager null delete

本文链接：https://blog.csdn.net/mengxiang209/article/details/6906002

版权

ORACLE New Features 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

从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;

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

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.