我的实验环境：
- OS : Oracle Enterprise Linux 5.5 64Bit
- DB Type : Oracle Restart
- DB Version : 11.2.0.3
我用到的脚本：
查看事务信息 [oracle@maa3 ~]$ cat showtra.sql select XIDUSN, XIDSLOT, XIDSQN, UBAFIL, UBABLK, UBASQN, UBAREC, STATUS from v$transaction where ADDR = ( SELECT TADDR FROM V$SESSION WHERE SID=&SID ) / 查看rowid [oracle@maa3 ~]$ cat showrowid.sql select rowid, dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, dbms_rowid.rowid_row_number(rowid) num, rowidtochar(rowid) from &table_name where id=&id /
1、Update与Undo
luocs@MAA> select * from ltb order by 1; ID NAME ---------- ---------------------------------------- 1 XIAOKUN 2 ORACLE 3 LUOCS 4 EXADATA 查看当前窗口的SID luocs@MAA> select distinct sid from v$mystat; SID ---------- 58 查找ID为1的行所在BLOCK luocs@MAA> select rowid, dbms_rowid.rowid_relative_fno(rowid) as fno, dbms_rowid.rowid_block_number(rowid) as blkno from ltb where id=1; ROWID FNO BLKNO ------------------ ---------- ---------- AAAGA8AAIAAAADOAAA 8 206 更新ID为1的行，但不提交 luocs@MAA> update ltb set name='www.luocs.com' where id=1; 1 row updated. DUMP出该块 sys@MAA> alter system dump datafile 8 block 206; System altered. sys@MAA> select value from v$diag_info where name = 'Default Trace File'; VALUE ---------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_23625.trc 查看TRACE，部分内容略 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.008.000002fb 0x00c0008d.00de.2a ---- 1 fsc 0x0000.00000000 0x02 0x000a.01a.000002f5 0x00c00291.00ec.29 C--- 0 scn 0x0000.003fdb37 bdba: 0x020000ce data_block_dump,data header at 0x2ad7e149ea64 =============== tsiz: 0x1f98 hsiz: 0x16 pbl: 0x2ad7e149ea64 76543210 flag=-------- ntab=1 nrow=2 frre=-1 fsbo=0x16 fseo=0x1f06 avsp=0x1f61 tosp=0x1f61 0xe:pti[0] nrow=2 offs=0 0x12:pri[0] offs=0x1f06 0x14:pri[1] offs=0x1f67 block_row_dump: tab 0, row 0, @0x1f06 tl: 20 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [13] 77 77 77 2e 6c 75 6f 63 73 2e 63 6f 6d tab 0, row 1, @0x1f67 tl: 13 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 03 col 1: [ 6] 4f 52 41 43 4c 45 end_of_block_dump -- 主要看Itl为0x01的行0x01 0x0009.008.000002fb 0x00c0008d.00de.2a ---- 1 fsc 0x0000.00000000 其中xid=0x0009.008.000002fb 而uba=0x00c0008d.00de.2a uba=DBA.seq#.rec# Dba=0x00c0008d , 通过dba获得undo的file和block sys@MAA> select dbms_utility.data_block_address_file(to_number('c0008d','xxxxxxxxxxxx'))file#, 2 dbms_utility.data_block_address_block(to_number('c0008d','xxxxxxxxxxxx'))block# 3 from dual; FILE# BLOCK# ---------- ---------- 3 141 这和v$transaction里该事物的ubafil和ubablk值完全相符。 sys@MAA> @showtra Enter value for sid: 58 old 13: WHERE SID=&SID new 13: WHERE SID=58 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- 9 8 763 3 141 222 42 ACTIVE 验证file 3就是Undo sys@MAA> select file_name from dba_data_files where file_id=3; FILE_NAME ---------------------------------------------------------------------------------------------------- +MSDATA/maa/datafile/undotbs1.262.792009883 dump该undo块 sys@MAA> alter system dump datafile 3 block 141; System altered. 查看TRACE，部分内容略 ******************************************************************************** UNDO BLK: xid: 0x0009.008.000002fb seq: 0xde cnt: 0x2a irb: 0x2a icl: 0x0 flg: 0x0000 -- 找到xid: 0x0009.008.000002fb的事物，cnt: 0x2a irb: 0x2a 等于上面uba的 rec# 直接找到rec #0x2a *----------------------------- * Rec #0x2a slt: 0x08 objn: 24636(0x0000603c) objd: 24636 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x00c0008d.00de.29 ctl max scn: 0x0000.00400a3b prv tx scn: 0x0000.00400a45 txn start scn: scn: 0x0000.004015b5 logon user: 59 prev brb: 12583049 prev bcl: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x0003.00e.000002e4 uba: 0x00c00407.0122.08 flg: C--- lkc: 0 scn: 0x0000.003f86e4 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x020000ce hdba: 0x020000ca itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: -6 col 1: [ 7] 58 49 41 4f 4b 55 4e -- col 1: [ 7] 58 49 41 4f 4b 55 4e 我们转换一下： sys@MAA> declare i varchar2(50); 2 begin 3 dbms_stats.convert_raw_value('5849414f4b554e',i); 4 dbms_output.put_line(i); 5 end; 6 / XIAOKUN PL/SQL procedure successfully completed. -- 可见它就是我们更新语句的前镜像。
2、Delete与Undo
上面注解中已经有说明，这里部分说明略掉
新窗口，查看SID luocs@MAA> select distinct sid from v$mystat; SID ---------- 49 luocs@MAA> select rowid, dbms_rowid.rowid_relative_fno(rowid) as fno, dbms_rowid.rowid_block_number(rowid) as blkno from ltb where id=4; ROWID FNO BLKNO ------------------ ---------- ---------- AAAGA8AAIAAAADLAAB 8 203 delete一条记录 luocs@MAA> delete from ltb where id=4; 1 row deleted. sys@MAA> alter system dump datafile 8 block 203; System altered. 查看TRACE，部分内容略 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0007.00b.00000305 0x00c003af.0183.1e C--- 0 scn 0x0000.004015b5 0x02 0x0005.00a.00000315 0x00c0021c.01ac.21 ---- 1 fsc 0x000c.00000000 bdba: 0x020000cb data_block_dump,data header at 0x2b9e211eea64 =============== tsiz: 0x1f98 hsiz: 0x16 pbl: 0x2b9e211eea64 76543210 flag=-------- ntab=1 nrow=2 frre=-1 fsbo=0x16 fseo=0x1f70 avsp=0x1f68 tosp=0x1f76 0xe:pti[0] nrow=2 offs=0 0x12:pri[0] offs=0x1f8c 0x14:pri[1] offs=0x1f70 block_row_dump: tab 0, row 0, @0x1f8c tl: 12 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 04 col 1: [ 5] 4c 55 4f 43 53 tab 0, row 1, @0x1f70 tl: 2 fb: --HDFL-- lb: 0x2 end_of_block_dump -- 主要看ITL=0x02的行0x02 0x0005.00a.00000315 0x00c0021c.01ac.21 ---- 1 fsc 0x000c.00000000 XID=0x0005.00a.00000315 UBA=0x00c0021c.01ac.21 同样获得undo的file 和block sys@MAA> select dbms_utility.data_block_address_file(to_number('c0021c','xxxxxxxxxxxx'))file#, 2 dbms_utility.data_block_address_block(to_number('c0021c','xxxxxxxxxxxx'))block# 3 from dual; FILE# BLOCK# ---------- ---------- 3 540 dump出undo块 sys@MAA> alter system dump datafile 3 block 540; ******************************************************************************** UNDO BLK: xid: 0x0005.00a.00000315 seq: 0x1ac cnt: 0x21 irb: 0x21 icl: 0x0 flg: 0x0000 也可以看出cnt: 0x21 irb: 0x21 和上面UBA的REC#相等 直接找到REC #0x21 *----------------------------- * Rec #0x21 slt: 0x0a objn: 24636(0x0000603c) objd: 24636 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x00c0021c.01ac.1f ctl max scn: 0x0000.00400e76 prv tx scn: 0x0000.00400e77 txn start scn: scn: 0x0000.00000000 logon user: 59 prev brb: 12583431 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z KDO Op code: IRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x020000cb hdba: 0x020000ca itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 1(0x1) size/delt: 14 fb: --H-FL-- lb: 0x0 cc: 2 null: -- col 0: [ 2] c1 05 col 1: [ 7] 45 58 41 44 41 54 41 转换col 1: [ 7] 45 58 41 44 41 54 41 sys@MAA> declare i varchar2(50); 2 begin 3 dbms_stats.convert_raw_value('45584144415441',i); 4 dbms_output.put_line(i); 5 end; 6 / EXADATA PL/SQL procedure successfully completed. -- 可见就是delete的前镜像
3、Insert与Undo
luocs@MAA> select sid from v$mystat where rownum=1; SID ---------- 1 luocs@MAA> insert into ltb values(5,'AI.SI.NI.LE'); 1 row created. luocs@MAA> select rowid, dbms_rowid.rowid_relative_fno(rowid) as fno, dbms_rowid.rowid_block_number(rowid) as blkno from ltb where id=5; ROWID FNO BLKNO ------------------ ---------- ---------- AAAGA8AAIAAAADOAAC 8 206 sys@MAA> alter system dump datafile 8 block 206; Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.008.000002fb 0x00c0008d.00de.2a ---- 1 fsc 0x0000.00000000 0x02 0x0002.01a.000002ee 0x00c00097.013b.08 ---- 1 fsc 0x0000.00000000 -- 现在发现itl 0x01和0x02都给事务持有，而xid=0x0002.01a.000002ee（usn=2,slot=42,seq=750）为insert所触发的事务id sys@MAA> @showtra Enter value for sid: 1 old 13: WHERE SID=&SID new 13: WHERE SID=1 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- 2 26 750 3 151 315 8 ACTIVE -- 再看uba=0x00c00097.013b.08,获得undo file和block sys@MAA> select dbms_utility.data_block_address_file(to_number('c00097','xxxxxxxxxxxx'))file#, 2 dbms_utility.data_block_address_block(to_number('c00097','xxxxxxxxxxxx'))block# 3 from dual; FILE# BLOCK# ---------- ---------- 3 151 bdba: 0x020000ce data_block_dump,data header at 0x2aed623c3a64 =============== tsiz: 0x1f98 hsiz: 0x18 pbl: 0x2aed623c3a64 76543210 flag=-------- ntab=1 nrow=3 frre=-1 fsbo=0x18 fseo=0x1ef4 avsp=0x1f4d tosp=0x1f4d 0xe:pti[0] nrow=3 offs=0 0x12:pri[0] offs=0x1f06 0x14:pri[1] offs=0x1f67 0x16:pri[2] offs=0x1ef4 block_row_dump: tab 0, row 0, @0x1f06 tl: 20 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [13] 77 77 77 2e 6c 75 6f 63 73 2e 63 6f 6d tab 0, row 1, @0x1f67 tl: 13 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 03 col 1: [ 6] 4f 52 41 43 4c 45 tab 0, row 2, @0x1ef4 tl: 18 fb: --H-FL-- lb: 0x2 cc: 2 col 0: [ 2] c1 06 col 1: [11] 41 49 2e 53 49 2e 4e 49 2e 4c 45 end_of_block_dump -- 我们新插入的行值批不匹配 luocs@MAA> select dump(5,16) from dual; DUMP(5,16) ---------------------------------- Typ=2 Len=2: c1,6 luocs@MAA> select dump('AI.SI.NI.LE',16) from dual; DUMP('AI.SI.NI.LE',16) ---------------------------------------------------------------------------------------------- Typ=96 Len=11: 41,49,2e,53,49,2e,4e,49,2e,4c,45 我们dump undo块 sys@MAA> alter system dump datafile 3 block 151; ******************************************************************************** UNDO BLK: xid: 0x0002.01a.000002ee seq: 0x13b cnt: 0x8 irb: 0x8 icl: 0x0 flg: 0x0000 跳到Rec #0x8 *----------------------------- * Rec #0x8 slt: 0x1a objn: 24636(0x0000603c) objd: 24636 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x00c00097.013b.05 ctl max scn: 0x0000.00400e91 prv tx scn: 0x0000.00400f10 txn start scn: scn: 0x0000.00401a99 logon user: 59 prev brb: 12583060 prev bcl: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x000a.01a.000002f5 uba: 0x00c00291.00ec.29 flg: C--- lkc: 0 scn: 0x0000.003fdb37 KDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x020000ce hdba: 0x020000ca itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 2(0x2) 从上面信息中找到： objn: 24636(0x0000603c) -> rowid的 data_object_id bdba: 0x020000ce -> rowid的file#, block# slot: 2(0x2) -> rowid的row_number sys@MAA> select to_number('0000603c','xxxxxxxxxxxxxxxxx') from dual; TO_NUMBER('0000603C','XXXXXXXXXXXXXXXXX') ----------------------------------------- 24636 sys@MAA> select dbms_utility.data_block_address_file(to_number('20000ce','xxxxxxxxxxxx'))file#, 2 dbms_utility.data_block_address_block(to_number('20000ce','xxxxxxxxxxxx'))block# 3 from dual; FILE# BLOCK# ---------- ---------- 8 206 luocs@MAA> @showrowid Enter value for table_name: ltb old 7: from &table_name new 7: from ltb Enter value for id: 5 old 8: where id=&id new 8: where id=5 ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID) ------------------ ---------- ---------- ---------- ---------- ------------------------------------ AAAGA8AAIAAAADOAAC 24636 8 206 2 AAAGA8AAIAAAADOAAC -- 可见一一对上。
总结：
1、update : 在Undo中记录被更新列的前镜像和被更新行ROWID；
2、delete : 在Undo中记录被删除行所有列的前镜像和其ROWID；
3、insert : 在Undo中记录插入行的ROWID。
转载自：http://www.luocs.com/archives/553.html
151
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
