【赵渝强老师】Oracle的闪回版本查询

原创

赵渝强老师

发布于 2025-03-29 01:37:10

780

文章被收录于专栏：OracleOracle

Oracle数据库的闪回版本查询（Flashback Version Query）提供了一个审计行改变的查询功能，通过它可以查找到所有已经提交了的行记录。视频讲解如下：

一、闪回版本查询简介

使用闪回版本查询可以获取在给定的时间区间中，指定的行的不同版本。当commit语句被执行时，一个新的行版本将被创建。闪回版本查询使用了select语句的versions between子句来指定时间区间。闪回版本查询的语法格式如下所示：

select 列1,列2,.....
from 表名
versions between [scn|timestamp] [表达式|minvalue]
         and [表达式|maxvalue] as of [scn|timestamp] 表达式;

# 其中：
# between...and：用于指定时间区间。
# scn|timestamp：用于指定使用SCN，还是使用时间戳。
# as of：用于恢复单个版本的数据。
# minvalue：代表SCN或者时间戳的最小值。
# maxvalue：代表SCN或者时间戳的最大值。

二、【实战】在查询中使用闪回版本查询

在了解到了什么是Oracle数据库的闪回版本查询后，下面将通过一个具体是示例来演示如何在查询中使用闪回版本查询。

（1）使用c##scott用户登录数据库

sqlplus c##scott/tiger

（2）创建一张表用于测试闪回版本查询，并往表中插入数据。

SQL> create table flashback2(tid number,tname varchar2(20));
SQL> insert into flashback2 values(1,'Tom');
SQL> commit;
SQL> insert into flashback2 values(2,'Mary');
SQL> commit;
SQL> insert into flashback2 values(3,'Mike');
SQL> commit;
SQL> update flashback2 set tname='Mary123' where tid=2;
SQL> commit;

# 由于这里执行了四次commit语句，
# 因此在表flashback2上产生四个版本的信息。

（3）执行一个简单的查询语句。

SQL> select * from flashback2;

# 输出的信息如下：
TID 	TNAME
-------------- --------------------
	 1 		Tom
	 2 		Mary123
	 3 		Mike

# 由于这里的查询没有指定具体的版本信息，
# 因此在默认情况下将查询表的最新版本数据。

（4）执行闪回版本查询获取表上所有的历史版本信息。

SQL> select * from flashback2
      versions between scn minvalue and maxvalue;

# 输出的信息如下：
TID 	TNAME
------------- --------------------
	 2 		Mary123
	 3 		Mike
	 2 		Mary
	 1 		Tom

# 通过指定关键字versions表明将执行闪回版本查询，
# scn minvalue and maxvalue用于指定查询版本的区间范围。
# 这里的范围是从最小的SCN号查询到最大的SCN号，
# 即：查询表上面的所有版本，这里一共返回了4个版本。

（5）使用闪回版本查询的伪列获取每个版本的详细信息。

SQL> select tid,tname,
     versions_starttime,versions_endtime,versions_operation
     from flashback2
     versions between scn minvalue and maxvalue
     order by 1,3;

# 输出的信息如下：
TID		TNAME		VERSIONS_STARTTIME		VERSIONS_ENDTIME		VERSIONS_OPERATION
----- ---------- ------------------------- ----------------------- ------------------
 1 		Tom																I
 2 		Mary 		25-MAR-25 11.00.23 AM	25-MAR-25 11.00.38 AM		I
 2 		Mary123		25-MAR-25 11.00.38 AM								U
 3 		Mike 		25-MAR-25 11.00.32 AM								I

# 其中的伪列：
# VERSIONS_STARTTIME：代表当前版本的起始时间。
# VERSIONS_ENDTIME：代表当前版本的结束时间。
# VERSIONS_OPERATION：代表当前版本执行的具体操作。

# 由于在第（2）步操作中执行了一条update语句将Mary改了Mary123，
# 因此可以看出Mary的生命周期是
# 从25-MAR-25 11.00.23 AM到25-MAR-25 11.00.38 AM。