flashback query的介绍：

flashback query可以查询表的历史记录，至于多长时间是由undo_retention决定的。如果
undo的信息没有被overwrite的话，可以查询更长时间。

案例：

下面是一个用户错误地更改了表的
数据，通过flashback query可以得到更改之前的数据。

点击(此处)折叠或打开

select * from dbs101.payment as of timestamp sysdate-60/1440 where pid = 100
union all
select * from dbs101.payment where pid = 100
create table dbs101.payment_bak as select select * from dbs101.payment as of
timestamp sysdate-60/1440 where pid = 100;
update dbs101.payment set amount = (
select amount from dbs101.payment_bak b
where dbs101.payment.id = b.id
)
and dbs101.payment.id = 100;
commit;

在这里可以取到1个小时之前的数据，这是因为undo segment的信息还没有被覆盖。

当事务没有提交时，可以从视图v$transaction中找到usn，slot，sqn，undo的文件和块。

点击(此处)折叠或打开

select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
SQL> select usn,name from v$rollname where usn=51;
USN NAME
---------- ------------------------------
51 _SYSSMU51$

dump出undo segment头。
alter system dump undo header '_SYSSMU51$';

点击(此处)折叠或打开

TRN CTL:: seq: 0x20c0 chd: 0x002e ctl: 0x0014 inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x46800319.20c0.12 scn: 0x0418.149521f0
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.20c0.11 ext: 0x2 spc: 0x1848
uba: 0x00000000.20c0.02 ext: 0x2 spc: 0x1ef6
uba: 0x00000000.20be.26 ext: 0x0 spc: 0xa2a
uba: 0x00000000.1f16.02 ext: 0x4 spc: 0x1f1e
uba: 0x00000000.1f16.0b ext: 0x4 spc: 0x1b0c
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x9ea1 0x0003 0x0418.14952377 0x46800318 0x0000.000.00000000 0x00000001 0x00000000 1312888300
......
0x0c 10 0x80 0x9e77 0x0002 0x0418.14952662 0x46800319 0x0000.000.00000000 0x00000001 0x00000000 0
......

可以看到ITL表中0x0c记录是当前的记录。

再dump出undo文件块的内容：

点击(此处)折叠或打开

alter system dump datafile 282 block 793;
uba: 0x46800319.20c0.10 ctl max scn: 0x0418.149521cd prv tx scn: 0x0418.149521f0
txn start scn: scn: 0x0418.149523e8 logon user: 569
prev brb: 1182794519 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x012afa9c hdba: 0x012afa9b
itli: 2 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 2] c1 03

这里记录了更新前的数据。

再dump出数据块的内容：

点击(此处)折叠或打开

select file_id,block_id from dba_extents where segment_name='PAYMENT';
SQL> select file_id,block_id,blocks from dba_extents where segment_name='PAYMENT';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
4 2816665 8
alter system dump datafile 4 block min 2816665 block max 2816673;
---------data file block dump
Block header dump: 0x012afa9c
Object id on Block? Y
seg/obj: 0x2241b8 csc: 0x418.149526b4 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x12afa99 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0035.01a.0000a9e6 0x468000ed.2fb1.26 C--- 0 scn 0x0418.14952662
0x02 0x0033.00c.00009e77 0x46800319.20c0.12

这里记录了Itl列表。Xid，Uba等等信息。

执行commit和checkpoint操作，发现Itl并没有改变。

点击(此处)折叠或打开

Block header dump: 0x012afa9c
Object id on Block? Y
seg/obj: 0x2241b8 csc: 0x418.149526b4 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x12afa99 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0035.01a.0000a9e6 0x468000ed.2fb1.26 C--- 0 scn 0x0418.14952662
0x02 0x0033.00c.00009e77 0x46800319.20c0.12 ---- 1 fsc 0x0000.00000000
alter system checkpoint;
Block header dump: 0x012afa9c
Object id on Block? Y
seg/obj: 0x2241b8 csc: 0x418.149526b4 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x12afa99 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0035.01a.0000a9e6 0x468000ed.2fb1.26 C--- 0 scn 0x0418.14952662
0x02 0x0033.00c.00009e77 0x46800319.20c0.12

当过了12个小时候，在看看dump的内容。

点击(此处)折叠或打开

Block header dump: 0x012afa9c
Object id on Block? Y
seg/obj: 0x2241b8 csc: 0x418.1495598e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x12afa99 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0035.01a.0000a9e6 0x468000ed.2fb1.26 C--- 0 scn 0x0418.14952662
0x02 0x0033.00c.00009e77 0x46800319.20c0.12 C-U- 0 scn 0x0418.14953b13

Itl的scn和lck发生了改变。

再执行flashquery，发现rollback segment已经过期了。

点击(此处)折叠或打开

SQL> select * from dbs101.payment as of timestamp sysdate-900/1440 ;
select * from dbs101.payment as of timestamp sysdate-900/1440

ORA-01555: snapshot too old: rollback segment number 51 with name "_SYSSMU51$" too small

再看看undo segment header的scn。

点击(此处)折叠或打开

undo segment header: ITL
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x0c 9 0x00 0x9eb0 0x002a 0x0418.14974f68 0x46800517 0x0000.000.00000000 0x00000001 0x00000000 1312942722

0x0418.14974f68 比data block中的scn 0x0418.14953b13要大，这说明undo segment已经被overwrite了。

注释：这里做这么多的dump是为了更好地理解flashback query怎么从undo中获取历史的信息。

ITPub博客

flashback query

最新文章