Undo internal<1>
来源：互联网 发布：深圳网络推广方法 编辑：程序博客网 时间：2024/06/06 09:59
v$transaction: 列出了没有提交的活动事务2. XID的结构egSQL> ---session1SQL> drop table test_undo1 purge;Table dropped.SQL> create table test_undo1 as select * from dba_objects;Table created.SQL> set linesize 200SQL> 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 test_undo1 where object_id = 1051; 2 3 4 5 6 7 8 ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)------------------ ---------- ---------- ---------- ---------- ------------------AAApgPAALAAACkWAAC 169999 11 10518 2 AAApgPAALAAACkWAACSQL> select sid from v$mystat where rownum<2; SID---------- 1625SQL> delete from test_undo1 where object_id=1051;1 row deleted.SQL> SQL> 产生一个事务:SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec,to_char(START_SCN,'999999999999') from v$transaction; XIDUSN XIDSLOT XIDSQN UBABLKUBAFIL UBAREC TO_CHAR(START---------- ---------- ---------- ---------- ---------- ---------- ------------- 1 17 173289303 2 29 57693468601其中XINUSN 表示回滚段号Undo Segement numberXIDSLOT 事务槽号XIDSQN 事务编号UBABLK UBA 块号-----dump 数据块SQL> alter system dump datafile 11 block 10518;System altered.Block header dump: 0x02c02916 Object id on Block? Y seg/obj: 0x2980f csc: 0x0d.6ecc779f itc: 3 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000d.6ecc779f0x02 0x0001.011.0002a4e9 0x0080012f.4243.1d ---- 1 fsc 0x004e.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000data_block_dump,data header at 0x14055274===============tsiz: 0x1f88hsiz: 0xbapbl: 0x14055274bdba: 0x02c02916 76543210flag=--------ntab=1nrow=84frre=-1fsbo=0xbafseo=0x3ebavsp=0x331tosp=0x3810xe:pti[0] nrow=84 offs=00x12:pri[0] offs=0x1f350x14:pri[1] offs=0x1eea0x16:pri[2] offs=0x1e9a0x18:pri[3] offs=0x1e50其中Lck 表示锁住的行从上面我的数据块dump,我们可以发现，该事务所对应的ITL是0x02 .(因为lck是1,flag是---,表示未提交)Itl Xid Uba Flag Lck Scn/Fsc0x02 0x0001.011.0002a4e9 0x0080012f.4243.1d ---- 1 fsc 0x004e.00000000下面开始分解Xid:0x0001:回滚段编号,转换后为1,说明该事务使用的是第1号回滚段011:事务槽编号(slot),转换后为17,说明对应undo segment header的transaction table记录中的index是170002a4e9:173289序号(同一个事务可能具有多个SCN，实际上每一个DML操作都有一个SCN）实际上对应undo segment header中transaction table的wrap#值SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec,to_char(START_SCN,'9999999999999') from v$transaction; XIDUSN XIDSLOT XIDSQN UBABLKUBAFIL UBAREC TO_CHAR(START_---------- ---------- ---------- ---------- ---------- ---------- -------------- 1 17 173289303 2 29 5769346860SQL> select name from v$rollname where usn=1;NAME------------------------------_SYSSMU1$-------undo segment header dump (_SYSSMU8$)dump 回滚段:SQL> alter system dump undo header '_SYSSMU1$';System altered.********************************************************************************Undo Segment: _SYSSMU1$ (1)******************************************************************************** Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 271 last map 0x00000000 #maps: 0 offset: 4080 Highwater:: 0x00800131 ext#: 2 blk#: 40 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 2 Unlocked Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x0080000a length: 7 0x00801611 length: 8 0x00800109 length: 128 0x00800789 length: 128 Retention Table ----------------------------------------------------------- Extent Number:0 Commit Time: 1408576610 Extent Number:1 Commit Time: 1408576610 Extent Number:2 Commit Time: 0 Extent Number:3 Commit Time: 1408576610 TRN CTL:: seq: 0x4243 chd: 0x002a ctl: 0x0028 inc: 0x00000000 nfb: 0x0002 mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x0080012f.4243.1d scn: 0x000d.6ecc61a1Version: 0x01 FREE BLOCK POOL:: uba: 0x00000000.4243.1c ext: 0x2 spc: 0x1166 uba: 0x00800130.4243.02 ext: 0x2 spc: 0x1f06 uba: 0x00800131.4243.16 ext: 0x2 spc: 0xfb6 uba: 0x00000000.423d.01 ext: 0x2 spc: 0x1f88 uba: 0x00000000.2919.01 ext: 0x2 spc: 0x1f88 TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x0a 9 0x00 0x2a4f0 0x0008 0x000d.6ecc69cc 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408591580 0x0b 9 0x00 0x2a4e6 0x002f 0x000d.6ecc72a2 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1408597235 0x0c 9 0x00 0x2a4dd 0x000e 0x000d.6ecc6d05 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408593634 0x0d 9 0x00 0x2a4ea 0x0022 0x000d.6ecc6384 0x0080011d 0x0000.000.00000000 0x00000001 0x00000000 1408587630 0x0e 9 0x00 0x2a4ee 0x0017 0x000d.6ecc6e3b 0x0080011e 0x0000.000.00000000 0x00000001 0x00000000 1408594533 0x0f 9 0x00 0x2a4e6 0x000a 0x000d.6ecc694a 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408591232 0x10 9 0x00 0x2a4e1 0x0029 0x000d.6ecc728f 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1408597235 0x11 10 0x80 0x2a4e9 0x0002 0x000d.6ecc77b9 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 0 0x12 9 0x00 0x2a4f2 0x0028 0x000d.6ecc773f 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 1408600205 0x13 9 0x00 0x2a4eb 0x002c 0x000d.6ecc68e0 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408591232 0x14 9 0x00 0x2a4eb 0x001c 0x000d.6ecc6360 0x0080011d 0x0000.000.00000000 0x00000001 0x00000000 1408587630 0x15 9 0x00 0x2a4ea 0x000d 0x000d.6ecc6370 0x0080011d 0x0000.000.00000000 0x00000001 0x00000000 1408587630 0x16 9 0x00 0x2a4e9 0x0002 0x000d.6ecc7406 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 1408597835 0x17 9 0x00 0x2a44f 0x0019 0x000d.6ecc6e4b 0x0080011e 0x0000.000.00000000 0x00000001 0x00000000 1408594533 0x18 9 0x00 0x2a4f0 0x001d 0x000d.6ecc6548 0x0080011f 0x0000.000.00000000 0x00000001 0x00000000 1408588831 state 9标示inactive 10 标示active index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x11 10 0x80 0x2a4e9 0x0002 0x000d.6ecc77b9 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 0这一行中的 dba地址 标示的是undo block 0x11 ：17 :事务槽编号(slot),转换后为17,说明对应undo segment header中的transaction table记录中的index是17我们可以看到transaction table中状态处于active的slot是index 0x11 . 跟我们前面的内容刚好对上.大家注意看slot 0x11的wrap#值,是不是刚好对上前面的xid中的第3部分内容 ？cflags 表示什么？ 表示事务的状态,0x80表示未commituel 表示什么？ 表示事务当前所处于的extent区域(0x0002 ,转换为10进制后为2)scn 表示什么？ 该事务开始的scn,等于v$transaction.start_scn。SQL> select * from v$rollstat where usn=1; USNLATCH EXTENTS RSSIZEWRITES XACTSGETS---------- ---------- ---------- ---------- ---------- ---------- ---------- WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK---------- ---------- ---------- ---------- ---------- ---------- ---------- AVEACTIVE STATUS CUREXT CURBLK---------- --------------- ---------- ---------- 1 1 4 22200321404601 354 0 2220032 0 00 0 0 ONLINE 2 40可以可以发现，uel值正好等于v$rollstat.curext值(Current extent)= 2，0x0002表示第2个extent.SQL> select owner,segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name='_SYSSMU1$';OWNER SEGMENT_NAME EXTENT_ID FILE_IDBLOCK_ID BLOCKS---------- ------------ ---------- ---------- ---------- ----------SYS _SYSSMU1$ 0 2 9 8SYS _SYSSMU1$ 1 2 5649 8SYS _SYSSMU1$ 2 2 265128SYS _SYSSMU1$ 3 2 1929128BLOCK_ID ：extent的起始块blocks extent的块数和如下对应 0x0080000a length: 7 0x00801611 length: 8 0x00800109 length: 128 0x00800789 length: 128查看创建UNOD的sql： CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u03/oradata/jhoa/undotbs01.dbf' SIZE 26214400 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE ALTER DATABASE DATAFILE '/u03/oradata/jhoa/undotbs01.dbf' RESIZE 2537553920 SQL> select dbms_utility.data_block_address_file(TO_NUMBER('800109', 'XXXXXXXX')) file_id, dbms_utility.data_block_address_block(TO_NUMBER('800109', 'XXXXXXXX')) block_id from dual; 2 3 4 FILE_ID BLOCK_ID---------- ---------- 2 265SQL> select 265 + 128 from dual; 265+128---------- 393undo block 303正好落在265 --- 393这里的地址就是UBA： TRN CTL:: seq: 0x4243 chd: 0x002a ctl: 0x0028 inc: 0x00000000 nfb: 0x0002 mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x0080012f.4243.1d scn: 0x000d.6ecc61a110 标示active index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x11 10 0x80 0x2a4e9 0x0002 0x000d.6ecc77b9 0x0080012f 0x0000.000.00000000 0x00000001 0x00000000 0SQL> select dbms_utility.data_block_address_file(TO_NUMBER('080012f', 'XXXXXXXX')) file_id, dbms_utility.data_block_address_block(TO_NUMBER('080012f', 'XXXXXXXX')) block_id from dual; 2 3 4 FILE_ID BLOCK_ID---------- ---------- 2 303转换uba的地址后得到的块号是303 0 0
- Undo internal<1>
- Undo Internal<2>
- Undo Internal的研究
- 【internal】undo内部机制
- 关于Undo Internal的研究
- 理解UNDO-1
- UNDO
- Undo
- undo
- undo
- Undo
- undo
- Undo
- innodb undo--update undo log补充信息1
- MFC Internal--part 1
- Internal 关键字使用(1)
- internal block structure(1)
- Git Internal #1：内容管理
- XDOJ1156 - 游戏里的碰撞
- Face your past without regret. Handle your present with confidence.Prepare for future without fear.
- 用 VC6 制作 WIN7/8 下以管理员权限运行的程序
- 佛祖保佑，永无BUG
- Ubuntu 安装 eclipse
- Undo internal<1>
- 半年总结篇
- dfs.datanode.max.xcievers
- windows下搭建SVN服务器及配置myeclipse SVN客户端
- 线程的Interrupted
- Eclipse for PHP Developers + xamp +xdebug
- poj-1850
- QTCreator3.0.0的BUG。。。竟然写铁 鞋两个字。。后面标点符号会显示不见（其实在)。。。
- HDU 4160 Dolls(DAG最小路径覆盖)
