flashback query的介绍:
flashback query可以查询表的历史记录,至于多长时间是由undo_retention决定的。如果
undo的信息没有被overwrite的话,可以查询更长时间。
案例:
下面是一个用户错误地更改了表的
数据,通过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并没有改变。
执行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已经过期了。
ORA-01555: snapshot too old: rollback segment number 51 with name "_SYSSMU51$" too small
点击(此处)折叠或打开
- 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博客 ” ,链接:http://blog.itpub.net/25105315/viewspace-704572/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25105315/viewspace-704572/