当系统 INI 参数 ENABLE_FLASHBACK 置为 1 时,闪回功能开启,可以进行闪回查询。
DM MPP、DMDSC 和 DMDPC 环境不支持闪回查询。
17.1 闪回查询子句
闪回查询子句的语法,是在数据查询语句(参考第 4 章 数据查询语句)的基础上,为 FROM 子句增加了闪回查询子句。
语法格式
<闪回查询子句>::=
WHEN <TIMESTAMP time_exp> |
AS OF <TIMESTAMP time_exp> |
AS OF <SCN lsn>
参数
1.time_exp 一个日期表达式,一般用字符串方式表示
2.lsn 指定 LSN 值
图例
闪回查询子句
语句功能
用户通过闪回查询子句,可以得到指定表过去某时刻的结果集。指定条件可以为时刻或 LSN。
使用说明
- 闪回查询只支持普通表(包括加密表与压缩表)、水平分区表和堆表,不支持临时表、列存储表、外部表与视图;
- 闪回查询中 lsn 的值,可以通过查询动态视图 V$RLOG 或 V$LSN_TIME 来确定,也可以通过闪回版本查询(见下节)的伪列来确定;
- 由于视图 V$LSN_TIME 每三秒收集一次 LSN/TIME 映射关系,因此基于时间进行闪回查询时可能存在三秒的误差,如果需要进行精确度更高的闪回查询,建议基于 LSN 进行闪回查询。
举例说明
例 1 闪回查询特定时刻的 PERSON_TYPE 表。
查询 PERSON_TYPE 表。
SELECT * FROM PERSON.PERSON_TYPE;
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
在 2012-01-01 12:22:49 时刻插入数据,并提交。
INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('防损员');
INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('保洁员');
COMMIT;
SELECT * FROM PERSON.PERSON_TYPE;
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 防损员
6 保洁员
使用闪回查询取得 2012-01-01 12:22:45 时刻的数据。此时刻在插入数据的操作之前,可见此时的结果集不应该有 2012-01-01 12:22:49 时刻插入的数据。
SELECT * FROM PERSON.PERSON_TYPE WHEN TIMESTAMP '2012-01-01 12:22:45';
或
SELECT * FROM PERSON.PERSON_TYPE AS OF TIMESTAMP '2012-01-01 12:22:45';
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
在 2012-01-01 12:23:29 时刻删除数据,并提交。
DELETE FROM PERSON.PERSON_TYPE WHERE PERSON_TYPEID > 5;
COMMIT;
SELECT * FROM PERSON.PERSON_TYPE;
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 防损员
使用闪回查询得到删除前的数据。
SELECT * FROM PERSON.PERSON_TYPE WHEN TIMESTAMP '2012-01-01 12:23:00';
或
SELECT * FROM PERSON.PERSON_TYPE AS OF TIMESTAMP '2012-01-01 12:23:00';
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 防损员
6 保洁员
例 2 闪回查询指定 LSN 的 PERSON_TYPE 表。
查询 PERSON_TYPE 表。
SELECT * FROM PERSON.PERSON_TYPE;
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
通过查询 V$RLOG 视图的 CUR_LSN 字段来获取当前 LSN 值。
SELECT CUR_LSN FROM V$RLOG;
查询结果如下:
行号 CUR_LSN
------- ---------------------
1 85233
删除 PERSON_TYPE 表中的一行数据,并提交。
DELETE FROM PERSON.PERSON_TYPE WHERE PERSON_TYPEID=4;
COMMIT;
SELECT * FROM PERSON.PERSON_TYPE;
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
通过删除操作之前的 LSN 值进行闪回查询得到删除前的数据。
SELECT * FROM PERSON.PERSON_TYPE AS OF SCN 85233;
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
17.2 闪回版本查询
INI 参数 UNDO_RETENTION 设置了事务提交后回滚页保持时间,缺省为 90 秒。因此,超出 UNDO_RETENTION 时间之外的过期闪回版本,无法被闪回查询到。
语法格式
<闪回版本查询子句>::=VERSIONS BETWEEN <闪回版本查询条件>
<闪回版本查询条件>::=TIMESTAMP <time_exp1> AND <time_exp2> |
SCN <lsn1> AND <lsn2>
参数
1.time_exp 日期表达式,一般用字符串方式表示。<time_exp1> 表示起始时间,<time_exp2> 表示结束时间
2.lsn 指定 LSN 值。
使用说明
- 闪回版本查询支持普通表(包括加密表与压缩表)、临时表和堆表,不支持水平分区表、列存储表、外部表与视图;
- 支持将表 17.2.1 中的伪列作为闪回版本查询的查询项,辅助获取精准的闪回查询信息。
伪列 | 说明 |
---|---|
VERSIONS_STARTTIME、VERSIONS_STARTSCN、VERSIONS_START TRXID | 起始时间戳、起始 SN、起始 TRXID |
VERSIONS_ENDTIME、VERSIONS_END SCN、VERSIONS_ENDTRXID | 提交时间戳、提交 LSN、提交 TRXID。如果该值为 NULL,表示行版本仍然是当前版本 |
VERSIONS_OPERATION | 在行上的操作。I 表示 Insert、D 表示 Delete、U 表示 Update |
图例
闪回版本查询
语句功能
用户通过闪回版本查询子句,可以得到指定表过去某个时间段内,事务导致记录变化的全部记录。指定条件可以为时刻或 LSN。
举例说明
例 1 闪回版本查询指定时间段内,PERSON_TYPE 表的记录变化。
查询 PERSON_TYPE 表。
SELECT * FROM PERSON.PERSON_TYPE;
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 防损员
在 2021-12-28 10:05:05 时刻修改数据,并提交。
UPDATE PERSON.PERSON_TYPE SET NAME='保安员' WHERE PERSON_TYPEID=5;
COMMIT;
UPDATE PERSON.PERSON_TYPE SET NAME='收银员' WHERE PERSON_TYPEID=5;
COMMIT;
SELECT * FROM PERSON.PERSON_TYPE;
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 收银员
例 2 进行闪回版本查询,获得指定时间段内变化的记录。
SELECT VERSIONS_STARTSCN, VERSIONS_ENDSCN, NAME FROM PERSON.PERSON_TYPE VERSIONS BETWEEN TIMESTAMP '2021-12-28 10:05:05' AND SYSDATE;
查询结果如下:
VERSION_STARTSCN PERSON_TYPEID NAME
---------------- -------------- --------
41061 NULL 采购经理
41061 NULL 采购代表
41061 NULL 销售经理
41061 NULL 销售代表
41074 NULL 收银员
41071 41074 保安员
41061 41071 防损员
例 3 可以利用伪列 VERSION_STARTSCN 进行闪回查询得到修改前的数据。
SELECT * FROM PERSON.PERSON_TYPE AS OF SCN 41071;
查询结果如下:
PERSON_TYPEID NAME
------------- --------
1 采购经理
2 采购代表
3 销售经理
4 销售代表
5 保安员
17.3 闪回事务查询
闪回事务查询提供系统视图 V$FLASHBACK_TRX_INFO 供用户查看在事务级对数据库所做的更改。根据视图信息,可以确定如何还原指定事务或指定时间段内的修改。
使用说明
系统视图名为 V$FLASHBACK_TRX_INFO,定义如表 17.3.1 所示。
列名 | 数据类型 | 说明 |
---|---|---|
START_TRXID | BIGINT | 事务中第一个 DML 的 TRXID |
START_TIMESTAMP | TIMESTAMP | 事务中第一个 DML 的时间戳 |
COMMIT_TRXID | BIGINT | 提交事务的 TRXID |
COMMIT_TIMESTAMP | TIMESTAMP | 提交事务时的时间戳 |
LOGON_USER | VARCHAR(256) | 拥有事务的用户 |
UNDO_CHANGE# | INT | 记录修改顺序序号 |
OPERATION | CHAR(1) | DML 操作类型。 D:删除;U:修改;I:插入;N:更新插入(专门针对 CLUSTER PRIMARY KEY 的插入); C:事务提交;P:预提交记录;O:default |
TABLE_NAME | VARCHAR(256) | DML 修改的表 |
TABLE_OWNER | VARCHAR(256) | DML 修改表的拥有者 |
ROW_ID | ROWID | DML 修改行的 ROWID |
UNDO_SQL | VARCHAR(3900) | 撤销 DML 操作的 SQL 语句 |
COMMIT_LSN | BIGINT | 事务提交时的 LSN |
举例说明
例 查询指定时间之后的事务信息,可为闪回查询操作提供参考。
SELECT * FROM V$FLASHBACK_TRX_INFO WHERE COMMIT_TIMESTAMP > '2012-01-01 12:00:00';