Flashback Query利用oracle的一致性特性从undo表空间读取操作前的记录。不同事务在写数据时,会将数据的前映象写入undo段,如果同时又其他事务查询该表数据,可以通过undo表空间中数据的前映象来构造所需记录集,不需要等待写入事务提交或回滚。同一条记录的修改操作,可能在undo表空间中存在多条对应操作记录。
Flashback qurey选择的范围可以基于时间或scn,还能根据事物进行查询。
1、基于timestamp的查询(环境基于oracle10g 10.2.0.4 )
SCOTT@orcl>create table emp1 as selectename,sal,deptno from emp; ---scott用户创建emp1实验表
Table created.
SCOTT@orcl>select count(*) from emp1; ---emp1表当前有14条记录
COUNT(*)
----------
14
SCOTT@orcl>delete emp1 where deptno=10; ---删除deptno=10的所有记录
3 rows deleted.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>select count(*) from emp1; ---emp1表当前有14条记录
COUNT(*)
----------
11
SCOTT@orcl>select count(*) from emp1 as oftimestamp sysdate-2/1440; ---查询2分钟前emp1的记录
COUNT(*)
----------
14
SCOTT@orcl>insert into emp1 select * from emp1 asof timestamp sysdate-2/1440 where deptno=10; ---利用flashback query插入2分钟前删除的记录
3 rows created.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>selectcount(*) from emp1;
COUNT(*)
----------
14
2、基于scn的查询
SCOTT@orcl>conn/ as sysdba
Connected.
SYS@orcl>grant execute on dbms_flashback to scott; ---授权scott能执行dbms_flashback包
Grant succeeded.
SYS@orcl>connscott/oracle
Connected.
SCOTT@orcl>
SCOTT@orcl>selectdbms_flashback.get_system_change_number from dual; ---查询当前系统scn
GET_SYSTEM_CHANGE_NUMBER
------------------------
1095362
SCOTT@orcl>selectcount(*) from emp1;
COUNT(*)
----------
14
SCOTT@orcl>delete emp1 where deptno=20; ---删除emp1表deptno为20的记录
5 rows deleted.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>selectcount(*) from emp1;
COUNT(*)
----------
9
SCOTT@orcl>select count(*) from emp1 as of scn1095362; ---利用scn查询scn为1095362时emp1表的记录
COUNT(*)
----------
14
SCOTT@orcl>insert into emp1 select * from emp1 asof scn 1095362 where deptno=20; ---插入scn为1095362时删除的记录
5 rows created.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>selectcount(*) from emp1;
COUNT(*)
----------
14
使用scn比使用timestamp更加精确,使用timestamp做flashback query查询时,系统实际上把timestamp转换成scn,数据字典smon_scn_time可以查看scn和时间的对应关系,10g中系统每3秒产生一次系统时间与scn的匹配并存入sys.smon_scn_time表。
可以使用函数scn_to_timestamp和timestamp_to_scn进行scn和时间,时间和scn之间的转换。
3、查询操作的事务
查询某个时间段中表中记录做的修改可以使用flashback version query,version query中提供了几个伪列:
versions_startscn versions_starttime | 记录操作时的scn或者时间,如果为空比哦那个是记录在查询范围外创建 |
versions_endscn versions_endtime | 记录失效的scn或者时间,如果为空表示记录当前时间在当前表内不存在,或者已经删除。如果versions_operation列值为D,说明已被删除,如果为空说明这段记录这段时间无操作。 |
versions_xid | 该操作的事务ID |
versions_operation | 对行执行的操作:I表示insert,D表示delete,U表示update。对于索引键的update操作,version query可能会将其识别成两个操作:delete和insert。 |
下面用version query查询一段时间内行的修改记录:
SCOTT@orcl>selectdbms_flashback.get_system_change_number from dual; ---查询系统当前scn
GET_SYSTEM_CHANGE_NUMBER
------------------------
1096632
SCOTT@orcl>update emp1 set sal=100 whereename='SCOTT'; ---修改scott的sal记录3次
1 row updated.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>updateemp1 set sal=200 where ename='SCOTT';
1 row updated.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>updateemp1 set sal=300 where ename='SCOTT';
1 row updated.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>selectdbms_flashback.get_system_change_number from dual; ---查询修改完后的scn
GET_SYSTEM_CHANGE_NUMBER
------------------------
1096664
SCOTT@orcl>selectename,sal,versions_startscn,versions_endscn,versions_xid,versions_operationfrom emp1 versions between scn 1096632 and 1096664 where ename='SCOTT'; ---查询两个scn之间scott的sal改变的记录
ENAME SAL VERSIONS_STARTSCN VERSIONS_ENDSCNVERSIONS_XID V
-------------------- ----------------- --------------- ---------------- -
SCOTT 300 1096662 09001F0012020000U
SCOTT 200 1096658 1096662 0A000F00D0010000 U
SCOTT 100 1096648 1096658 02002A0003020000 U
SCOTT 3000 1096648
如果我们想把scott的工资还原到100,可以执行下面的sql:
SCOTT@orcl>updateemp1 set sal=(select sal from emp1 as of scn 1096648 where ename='SCOTT') whereename='SCOTT';
1 row updated.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>selectename,sal from emp1 where ename='SCOTT';
ENAME SAL
--------------------
SCOTT 100
当scn处于1096648和1096658之间任一值时,可以查询到scott的sal=100.
如果不清楚当时的scn和timestamp可以用minvalue和maxvlue来代替,比如:
SCOTT@orcl>selectename,sal,versions_startscn,versions_endscn,versions_xid,versions_operationfrom emp1 versions between scn minvalue and maxvalue where ename='SCOTT';
使用flashback_transaction_query视图可以查询一个事务所做的详细信息,可以使用上面查询出的VERSIONS_XID在flashback_transaction_query中进行查询。
SCOTT@orcl>descflashback_transaction_query;
Name Null? Type
------------------------------------------------- ----------------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
SCOTT@orcl>selectename,sal from emp1 where ename='SCOTT';
ENAME SAL
--------------------
SCOTT 100
SCOTT@orcl>deleteemp1 where ename='SCOTT';
1 row deleted.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>selectename,sal,versions_startscn,versions_endscn,versions_xid,versions_operationfrom emp1 versions between scn minvalue and maxvalue where ename='SCOTT'; ---查询删除scott时的事务id
ENAME SAL VERSIONS_STARTSCN VERSIONS_ENDSCNVERSIONS_XID V
-------------------- ----------------- --------------- ---------------- -
SCOTT 100 1097286 0900080013020000D
SCOTT 100 1097017 1097286 0900020013020000 U
SCOTT 300 1097017
SCOTT@orcl>conn/ as sysdba
Connected.
SYS@orcl>grant select any transaction to scott; ---授权scott有查询事务的权限
Grant succeeded.
SYS@orcl>connscott/oracle;
Connected.
SCOTT@orcl>selectxid,table_name,undo_sql from flashback_transaction_query where xid='0900080013020000';
---根据上面查询的事务id查询该事务的详细信息
XID
----------------
TABLE_NAME
--------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
0900080013020000
EMP1
insert into"SCOTT"."EMP1"("ENAME","SAL","DEPTNO")values ('SCOTT','100','20');
0900080013020000
XID
----------------
TABLE_NAME
--------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
查看undo_sql字段,可以发现撤销该操作的sql。
4、使用flashback query查询需要注意的事项
(1)必须启用automatic undo management,初始化参数undo_management为auto表示使用自动撤销管理表空间,manual表示手动管理。
SYS@orcl>showparameter undo;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
参数undo_retention指定undo段中数据保存的最短时间,以秒为单位。如果一个新事务开始的时候,undo表空间已经被写满,新事务的数据会自动覆盖已提交事务的数据,不管这些数据是否已过期。要确保undo_retention指定的时间一定有效可以使用下面命令,执行完后不会覆盖undo表空间中未过期的undo数据。
SYS@orcl>alter tablespace undotbs1retention guarantee;
(2)DDL操作的影响
- drop/modify列
- move表
- drop分区
- truncate table/pratition
这些操作会使undo表空间中的undo数据失效。