1.从DML更新事务开始
来看这个更新语句:
SQL> conn scott/scott
Connected.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 10 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL> select sal from emp where empno=7788;
SAL
----------
10
SQL> update emp set sal=4000 where empno=7788;
1 row updated.
SQL> select sal from emp where empno=7788;
SAL
----------
4000
先不提交这个事务,在另外窗口新开session,使用sys用户查询相关信息,进行进一步的分析
2.获得事务信息
从事务表中可以获得关于这个事务的信息,该事务位于9号回滚段(XIDUSN),在9号回滚段
上,该事务位于第29号事务槽(XIDSLOT):
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
9 29 385 1350 2 22
从v$rollstat视图中也可获得事务信息,xacts字段代表的是活动事务的数量,同样看到该事务
位于9号回滚段
SQL> select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat;
USN WRITES RSSIZE XACTS HWMSIZE SHRINKS WRAPS
---------- ---------- ---------- ---------- ---------- ---------- ----------
0 7620 385024 0 385024 0 0
1 21390 29351936 0 29351936 0 0
2 22108 3268608 0 3268608 0 0
3 29954 450560 0 450560 0 0
4 23700 843776 0 843776 0 0
5 23334 450560 0 450560 0 0
6 21082 450560 0 450560 0 0
7 23146 2285568 0 2285568 0 0
8 28742 843776 0 843776 0 1
9 22648 2088960 1 2088960 0 0
10 24326 2220032 0 2220032 0 0
11 rows selected.
获得回滚段名称并转储段头信息
查询v$rollname视图获得回滚段名称,并转储回滚段头信息:
SQL> select * from v$rollname a where a.usn=9;
USN NAME
---------- ------------------------------
9 _SYSSMU9$
SQL> alter system dump undo header '_SYSSMU9$';
System altered.
生成的跟踪文件如下:
SQL> select 2 d.value||'/'||lower(rtrim(i.instance, 3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 4 from ( select p.spid 5 from sys.v$mystat m, 6 sys.v$session s,sys.v$process p 7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 8 ( select t.instance from sys.v$thread t,sys.v$parameter v 9 where v.name = 'thread' and 10 (v.value = 0 or t.thread# = to_number(v.value))) i, 11 ( select value from sys.v$parameter 12 where name = 'user_dump_dest') d 13 / TRACE_FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/admin/jingyong/udump/jingyong_ora_2611.trc
4.获得跟踪文件信息
注意这就是前边多闪提到过的回滚段头信息,其中包括事务表信息,从以下的跟踪文件中,
可以清晰地看到这些内容:
*** 2013-01-07 08:19:09.209
********************************************************************************
Undo Segment: _SYSSMU9$ (9)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 255
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00800546 ext#: 10 blk#: 61 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 10
Unlocked
Map Header:: next 0x00000000 #extents: 17 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080008a length: 7
0x008000f1 length: 8
0x008001d1 length: 8
0x008001d9 length: 8
0x008001e1 length: 8
0x008001e9 length: 8
0x008001f1 length: 8
0x008001f9 length: 8
0x00800201 length: 8
0x00800289 length: 8
0x00800509 length: 128
0x00800011 length: 8
0x00800041 length: 8
0x00800061 length: 8
0x008000d9 length: 8
0x008000e9 length: 8
0x008000a9 length: 8
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1388711270
Extent Number:1 Commit Time: 1388711270
Extent Number:2 Commit Time: 1388711270
Extent Number:3 Commit Time: 1388711270
Extent Number:4 Commit Time: 1388711270
Extent Number:5 Commit Time: 1388711270
Extent Number:6 Commit Time: 1388711270
Extent Number:7 Commit Time: 1388711270
Extent Number:8 Commit Time: 1388711270
Extent Number:9 Commit Time: 1388712870
Extent Number:10 Commit Time: 0
Extent Number:11 Commit Time: 1388710789
Extent Number:12 Commit Time: 1388710854
Extent Number:13 Commit Time: 1388710854
Extent Number:14 Commit Time: 1388710854
Extent Number:15 Commit Time: 1388711270
Extent Number:16 Commit Time: 1388711270
TRN CTL:: seq: 0x0129 chd: 0x001e ctl: 0x0019 inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800546.0129.16 scn: 0x0000.000e4b58
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0129.15 ext: 0xa spc: 0x1440
uba: 0x00000000.0129.05 ext: 0xa spc: 0x1e08
uba: 0x00000000.0129.42 ext: 0xa spc: 0x73e
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0181 0x0004 0x0000.000e5e2d 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357510314
0x01 9 0x00 0x0181 0x0003 0x0000.000e570f 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357505284
0x02 9 0x00 0x0181 0x0009 0x0000.000e5c13 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357508885
0x03 9 0x00 0x0181 0x0007 0x0000.000e58ac 0x00800545 0x0000.000.00000000 0x00000002 0x00000000 1357506485
0x04 9 0x00 0x0181 0x0006 0x0000.000e5f7d 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286
0x05 9 0x00 0x0181 0x0002 0x0000.000e5c07 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357508885
0x06 9 0x00 0x0181 0x000a 0x0000.000e5f89 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286
0x07 9 0x00 0x0181 0x0008 0x0000.000e58b6 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357506485
0x08 9 0x00 0x0181 0x002e 0x0000.000e5a56 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357507685
0x09 9 0x00 0x0181 0x000b 0x0000.000e5c1e 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357508886
0x0a 9 0x00 0x0181 0x0011 0x0000.000e5f93 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286
0x0b 9 0x00 0x0181 0x002a 0x0000.000e5dba 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357510086
0x0c 9 0x00 0x0181 0x0000 0x0000.000e5dd4 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357510086
0x0d 9 0x00 0x0181 0x0010 0x0000.000e5fa8 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286
0x0e 9 0x00 0x0181 0x0016 0x0000.000e62f8 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357513687
0x0f 9 0x00 0x0181 0x0014 0x0000.000e6662 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357516088
0x10 9 0x00 0x0181 0x0012 0x0000.000e5fb2 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286
0x11 9 0x00 0x0181 0x000d 0x0000.000e5f9d 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286
0x12 9 0x00 0x0181 0x0017 0x0000.000e6150 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357512487
0x13 9 0x00 0x0181 0x002d 0x0000.000e64a1 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357514887
0x14 9 0x00 0x0180 0x001b 0x0000.000e67fe 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357517287
0x15 9 0x00 0x0181 0x000f 0x0000.000e6658 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357516088
0x16 9 0x00 0x0181 0x0013 0x0000.000e6301 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357513687
0x17 9 0x00 0x0181 0x000e 0x0000.000e615b 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357512487
0x18 9 0x00 0x0180 0x0029 0x0000.000e53b5 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357502883
0x19 9 0x00 0x0181 0xffff 0x0000.000e6813 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357517288
0x1a 9 0x00 0x0181 0x0015 0x0000.000e64ba 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357514888
0x1b 9 0x00 0x0181 0x0019 0x0000.000e6809 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357517288
0x1c 9 0x00 0x0180 0x001f 0x0000.000e4cff 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357498082
0x1d 10 0x80 0x0181 0x000a 0x0000.000e6858 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 0
0x1e 9 0x00 0x0180 0x001c 0x0000.000e4cf4 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357498082
0x1f 9 0x00 0x0180 0x0024 0x0000.000e4e9c 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357499280
0x20 9 0x00 0x0180 0x002f 0x0000.000e5059 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357500481
0x21 9 0x00 0x0180 0x0005 0x0000.000e5a6a 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357507685
0x22 9 0x00 0x0180 0x0023 0x0000.000e5044 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357500481
0x23 9 0x00 0x0180 0x0020 0x0000.000e504f 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357500481
0x24 9 0x00 0x0180 0x0022 0x0000.000e4ea6 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357499282
0x25 9 0x00 0x0180 0x0026 0x0000.000e5568 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357504084
0x26 9 0x00 0x0180 0x0001 0x0000.000e5705 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357505284
0x27 9 0x00 0x0180 0x0028 0x0000.000e5201 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357501683
0x28 9 0x00 0x0180 0x002c 0x0000.000e520c 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357501683
0x29 9 0x00 0x0180 0x002b 0x0000.000e5553 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357504083
0x2a 9 0x00 0x0180 0x000c 0x0000.000e5dc6 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357510086
0x2b 9 0x00 0x0180 0x0025 0x0000.000e555e 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357504084
0x2c 9 0x00 0x0180 0x0018 0x0000.000e53a9 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357502883
0x2d 9 0x00 0x0180 0x001a 0x0000.000e64ac 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357514888
0x2e 9 0x00 0x0180 0x0021 0x0000.000e5a60 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357507685
0x2f 9 0x00 0x0180 0x0027 0x0000.000e5064 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357500483
回顾前面的事务信息,该事务正好占用的是第29号事务槽(0x1d),状态(state)为10代表是活动的事务
SQL> select to_number('1d','xx') from dual ;
TO_NUMBER('1D','XX')
--------------------
29
5.转储前镜像信息
再来看DBA(Data Block Address),这个DBA指向的就是包含这个事务的前镜像的数据块地址
0x00800546
DBA代表数据块的存储地址,通过转换DBA计算出文件号和数据块号
SQL> SELECT DBMS_UTILITY.data_block_address_file (
2 TO_NUMBER (LTRIM ('0x00800546', '0x'), 'xxxxxxxx'))
3 AS file_no,
4 DBMS_UTILITY.data_block_address_block (
5 TO_NUMBER (LTRIM ('0x00800546', '0x'), 'xxxxxxxx'))
6 AS block_no
7 FROM DUAL;
FILE_NO BLOCK_NO
---------- ----------
2 1350
经过转换后,该前镜像信息位于file 2, block 1350,这和从事务表中查询得的数据完全一致:
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
9 29 385 1350 2 22
为了说明一些其他内容,继续先前scott用户的事务,再更新2条记录:
SQL> update emp set sal=4000 where empno=7782; 1 row updated. SQL> update emp set sal=4000 where empno=7698; 1 row updated.
将回滚段中的这个block转储出来:
SQL> alter system dump datafile 2 block 1350; System altered. SQL> select 2 d.value||'/'||lower(rtrim(i.instance, 3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 4 from ( select p.spid 5 from sys.v$mystat m, 6 sys.v$session s,sys.v$process p 7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 8 ( select t.instance from sys.v$thread t,sys.v$parameter v 9 where v.name = 'thread' and 10 (v.value = 0 or t.thread# = to_number(v.value))) i, 11 ( select value from sys.v$parameter 12 where name = 'user_dump_dest') d 13 / TRACE_FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/admin/jingyong/udump/jingyong_ora_4778.trc
这是跟踪文件开始部分的信息:
*** 2013-01-07 08:46:13.536 *** SERVICE NAME:(SYS$USERS) 2013-01-07 08:46:13.536 *** SESSION ID:(142.51) 2013-01-07 08:46:13.536 Start dump data blocks tsn: 1 file#: 2 minblk 1350 maxblk 1350 buffer tsn: 1 rdba: 0x00800546 (2/1350) scn: 0x0000.000e6ba3 seq: 0x01 flg: 0x00 tail: 0x6ba30201 frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK Hex dump of block: st=0, typ_found=1 ******************************************************************************** UNDO BLK: xid: 0x0009.01d.00000181 seq: 0x129 cnt: 0x18 irb: 0x18 icl: 0x0 flg: 0x0000
注意,这部分信息中有一个参数irb:0x18,irb指的是回滚段中记录的最近的没有提交变更的开始
之处,如果开始回滚,这是起始的搜索点.
接下来是回滚信息的偏移量,最后一个偏移地址正是0x18的信息
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1f3c 0x02 0x1ed0 0x03 0x1e54 0x04 0x1de8 0x05 0x1d3c 0x06 0x1c90 0x07 0x1c24 0x08 0x1b78 0x09 0x1acc 0x0a 0x1a20 0x0b 0x1974 0x0c 0x1908 0x0d 0x189c 0x0e 0x17f0 0x0f 0x1784 0x10 0x1718 0x11 0x166c 0x12 0x1600 0x13 0x1594 0x14 0x14e8 0x15 0x147c 0x16 0x13e4 0x17 0x1388 0x18 0x132c
那么我们可以找到Rec 0x18的信息:
*----------------------------- * Rec #0x18 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004) * Layer: 11 (Row) opc: 1 rci 0x17 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- KDO undo record: KTB Redo op: 0x02 ver: 0x01 op: C uba: 0x00800546.0129.17 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01000020 hdba: 0x0100001b itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191 ncol: 8 nnew: 1 size: 1 col 5: [ 3] c2 1d 33
从上面的信息中可以看到objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)
惹objn: 51148(0x0000c7cc) objd: 51148 是指object_id
tblspc: 4(0x00000004)是指表空间号4
SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=51148;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ----------------- -------------------
SCOTT EMP TABLE
SQL> select * from v$tablespace;
TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
6 EXAMPLE YES NO YES
3 TEMP NO NO YES
6 rows selected
SQL> select username,default_tablespace from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT USERS
通过上面的查询可以知道0x18的前镜像记录是记录的对scott用户的emp表修改的记录的前镜像信息
col 5: [ 3] c2 1d 33
c2 id 33转换为十进制是2850
SQL> select utl_raw.cast_to_number('c21d33') from dual;
UTL_RAW.CAST_TO_NUMBER('C21D33
------------------------------
2850
这就是下面的更新语句的前镜像信息,oracle就是这样通过回滚段保留前镜像信息的
SQL> update emp set sal=4000 where empno=7698;
SQL> select empno,sal from scott.emp where empno in(7788,7782,7698);
EMPNO SAL
---------- ----------
7698 2850
7782 2450
7788 10
在这条undo记录上,还记录一个数据rci,该参数代表的就是undo chain(同一个事务中的多次修改,
根据undo chain链接关联)的下一个偏移量,此处为0x17,找到0x17这条undo记录:
*----------------------------- * Rec #0x17 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004) * Layer: 11 (Row) opc: 1 rci 0x16 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- KDO undo record: KTB Redo op: 0x02 ver: 0x01 op: C uba: 0x00800546.0129.16 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01000020 hdba: 0x0100001b itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 6(0x6) flag: 0x2c lock: 0 ckix: 41 ncol: 8 nnew: 1 size: 1 col 5: [ 3] c2 19 33
这里记录的c2 19 33转换为十进制就是2450,是第二条更新记录的前镜像信息
SQL> update emp set sal=4000 where empno=7782;
SQL> select utl_raw.cast_to_number('c21933') from dual;
UTL_RAW.CAST_TO_NUMBER('C21933')
------------------------------
2450
这条undo记录中的rci指向下一条记录是0x16,找到0x16:
*-----------------------------
* Rec #0x16 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00800546.0129.14 ctl max scn: 0x0000.000e4b4d prv tx scn: 0x0000.000e4b58
txn start scn: scn: 0x0000.000e684e logon user: 54
prev brb: 8389956 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.00e.0000017e uba: 0x0080053d.0129.2f
flg: C--- lkc: 0 scn: 0x0000.000d444e
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000020 hdba: 0x0100001b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col 5: [ 2] c1 0b
这里的c10b转换十进制是10,正是第一条更新的记录:
SQL> update emp set sal=4000 where empno=7788;
SQL> select utl_raw.cast_to_number('c10b') from dual;
UTL_RAW.CAST_TO_NUMBER('C10B')
------------------------------
10
这是这个事务中最后一条更新数据,所以其实undo chain的指针rci为0x00,表示这是最后
一条记录.也可以从x$bh中找到这些数据块:
SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state 2 from x$bh a,dba_extents b 3 where b.relative_fno=a.dbarfil 4 and b.block_id< =a.dbablk and b.block_id+b.blocks>a.dbablk 5 and b.owner='SCOTT' and b.segment_name='EMP' order by a.dbablk asc; SEGMENT_NAME FILE# DBARFIL DBABLK CLASS STATE ----------------------- ---------- ---------- ---------- ---------- ---------- EMP 4 4 27 4 1 EMP 4 4 28 1 1 EMP 4 4 29 1 1 EMP 4 4 30 1 1 EMP 4 4 31 1 1 EMP 4 4 32 1 3 EMP 4 4 32 1 1 7 rows selected
注意class为4的是段头,class为1,块号为28到32的为数据块,如果此时在其他进程中查询
scott.emp表,oracle需要构造一致性读,通过前镜像把变化前的数据展现给用户:
SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 10.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 14 rows selected
再来查询哪些数据是脏数据
SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state, 2 decode(bitand(flag,1),0,'N','Y') dirty 3 from x$bh a,dba_extents b 4 where b.relative_fno=a.dbarfil 5 and b.block_id< =a.dbablk and b.block_id+b.blocks>a.dbablk 6 and b.owner='SCOTT' and b.segment_name='EMP' order by a.dbablk asc; SEGMENT_NAME FILE# DBARFIL DBABLK CLASS STATE DIRTY ------------------------- ---------- ---------- ---------- ---------- ---------- ----- EMP 4 4 27 4 1 N EMP 4 4 28 1 1 N EMP 4 4 29 1 1 N EMP 4 4 30 1 1 N EMP 4 4 31 1 1 N EMP 4 4 32 1 3 N EMP 4 4 32 1 3 N EMP 4 4 32 1 1 Y EMP 4 4 32 1 3 N 9 rows selected
注意此时,buffer cache中多出了两个数据块,也就是32存在4份,其中state为3的就是一致性读
构造的前镜像.dirty为Y的记录就是进行更改后没有提交的记录
6.转储数据块信息
在前镜像信息中,oracle还记录了前镜像对应的数据块的地址.可以从bdba记录中获得这部分信息
以上面数据为例bdba: 0x01000020记录了更改的数据块块地址.
SQL> SELECT DBMS_UTILITY.data_block_address_file (
2 TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))
3 AS file_no,
4 DBMS_UTILITY.data_block_address_block (
5 TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))
6 AS block_no
7 FROM DUAL;
FILE_NO BLOCK_NO
---------- ----------
4 32
0x01000020记录的正是file 4,block 32
现在将数据表中的block转储出来,看看其中的记录是什么样的信息:
SQL> alter system dump datafile 4 block 32; System altered. SQL> select 2 d.value||'/'||lower(rtrim(i.instance, 3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 4 from ( select p.spid 5 from sys.v$mystat m, 6 sys.v$session s,sys.v$process p 7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 8 ( select t.instance from sys.v$thread t,sys.v$parameter v 9 where v.name = 'thread' and 10 (v.value = 0 or t.thread# = to_number(v.value))) i, 11 ( select value from sys.v$parameter 12 where name = 'user_dump_dest') d 13 / TRACE_FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/admin/jingyong/udump/jingyong_ora_4859.trc
检查跟踪文件信息:
** 2013-01-07 09:27:18.032
*** SERVICE NAME:(SYS$USERS) 2013-01-07 09:27:18.030
*** SESSION ID:(142.53) 2013-01-07 09:27:18.029
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601
frmt: 0x02 chkval: 0xf364 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x01000020
Object id on Block? Y
seg/obj: 0xc7cc csc: 0x00.e6ebb itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01d.00000181 0x00800546.0129.18 ---- 3 fsc 0x0002.00000000
0x02 0x0002.010.00000158 0x00800598.013f.26 C--- 0 scn 0x0000.000d4495
其中scn表示提交commit scn,fsc表示快速提交scn
这里存在ITL事务槽信息,ITL事务槽指Interested Transaction List(ITL),事务必须
获得一个ITL事务槽才能进行数据修改,ITL内容主要包括:
Xid———Transaction ID;
Uba———Undo Block Address
Lck———Lock Status
注意:Xid=Undo.segment.number+transaction.table.slot.number+wrap
在上面的输出,看到itl1(0x01)上存在活动事务，将xid=0x0009.01d.00000181分解一下
该事务指向的回滚段号是9
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
9 29 385 1350 2 22
事务表的事务槽slot号为0x1d(转换为十进制是29)
SQL> select to_number('1d','xx') from dual;
TO_NUMBER('1D','XX')
--------------------
29
wrap#为0181正是dump回滚段看到的那个事务
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
......
0x1d 10 0x80 0x0181 0x000a 0x0000.000e6858 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 0
.....
可以看到,在数据块上同样存在指向回滚段的事务信息.
uba代表的是undo block address,指向具体的回滚段.可以看到ITL上uba=0x00800546.0129.18
将这个UBA进行分解:
0x00800546正是前镜像的数据块地址
seq:0129是顺序号
18是undo记录的开始地址(irb信息)
uba的内容和undo中的信息完全相符:
******************************************************************************** UNDO BLK: xid: 0x0009.01d.00000181 seq: 0x129 cnt: 0x18 irb: 0x18 icl: 0x0 flg: 0x0000
继续向下可以找到这3条被修改的记录,锁定位信息LB指向0x01号ITL事务槽:
tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4d 63
col 1: [ 5] 42 4c 41 4b 45
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 05 01 01 01 01
col 5: [ 2] c2 29
col 6: *NULL*
col 7: [ 2] c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4e 53
col 1: [ 5] 43 4c 41 52 4b
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 06 09 01 01 01
col 5: [ 2] c2 29
col 6: *NULL*
col 7: [ 2] c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4e 59
col 1: [ 5] 53 43 4f 54 54
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 bb 04 13 01 01 01
col 5: [ 2] c2 29
col 6: *NULL*
col 7: [ 2] c1 15
SQL> select utl_raw.cast_to_number('c229') from dual;
UTL_RAW.CAST_TO_NUMBER('C229')
------------------------------
4000
这个事务的过程如下:
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
......
0x1d 10 0x80 0x0181 0x000a 0x0000.000e6858 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 0
..... |
|
TX table slot
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01d.00000181 0x00800546.0129.18 ---- 3 fsc 0x0002.00000000
0x02 0x0002.010.00000158 0x00800598.013f.26 C--- 0 scn 0x0000.000d4495
ITL
data row1
data row2 回滚表空间的数据块-------------------------------Undo block
data row3 (UNDO BLK: |
xid: 0x0009.01d.00000181 |
seq: 0x129 cnt: 0x18 irb: 0x18 |
icl: 0x0 flg: 0x0000)
锁定位0x01 Rec #0x18 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 …
数据段 回滚段
(1)当一个事务开始时,需要在回滚段事务表上分配一个事务槽.
(2)在数据块头部获得一个ITL事务槽,该事务槽指向回滚段头的事务槽
(3)在修改数据之前,需要记录前镜像信息,这个信息以undo record的形式存储在回滚段中,
回滚段头事务槽指向该记录.
(4)锁定修改的行,修改行锁定位(lb-lock byte)指向ITL事务槽
(5)数据修改可以进行
这是一个事务的基本过程
7.块清除(Block Cleanouts)
当用户发出提交(commit)之后,oracle怎样来处理的.oracle是需要写出redo来保证故障时数据可以被
恢复,我们知道oracle并不需要在提交时就写出变更的数据块.那么在提交时,oracle会对数据块进行什么操作?
在事务需要修改数据时,必须分配ITL事务槽,必须锁定行,必须分配回滚段事务槽和回滚表空间来记录要修改
的数据的前镜像.当事务提交时,oracle需要将回滚段上的事务表信息标记为非活动,以便空间可以被重用
那么还有ITL事务信息和锁定信息需要清除,以记录提交.
由于oracle在数据块上存储了ITL和锁定等事务信息,所以oracle必须在事务提交之后清除这些事务数据,
这就是块清除.块清除主要清除的数据有行级锁,ITL信息(包括提交标志,scn等).
如果提交时修改过的数据块仍然在buffer cache中,那么oracle可以清除ITL信息,这种清除叫做快速块清除
(fast block cleanout),快速块清除还有一个限制,当修改的块数量超过buffer cache的10%,则对超出的部
分不再进行快速块清除.
如果提交事务时,修改过的数据块已经被写回到数据文件上(或大量修改超出10%的部分),再次读出该数据块
进行修改,显然成本过于高昂,对于这种情况,oracle选择延迟块清除(delayed block cleanout),等到下一次
访问该block时再来清除ITL锁定信息,这就是延迟块清除.oracle通过延迟块清除来提高数据库的性能,加快
提交操作.快速提交是最普遍的情况.来看一下延迟块清除的处理.
继续进行测试:
SQL> update emp set sal=4000 where empno=7788; 1 row updated. SQL> update emp set sal=4000 where empno=7782; 1 row updated. SQL> update emp set sal=4000 where empno=7698; 1 row updated.
更新完成之后,强制刷新buffer cache,将buffer cache中的数据都写出到数据文件:
SQL> alter session set events='immediate trace name flush_cache'; Session altered
此时再提交事务;
SQL> commit; Commit complete.
由于此时更新过的数据已经写出到数据文件,oracle将执行延迟块清除,将此时的数据块和回滚段转储出来:
[oracle@jingyong ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 10:18:56 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> alter system dump datafile 4 block 32; System altered. SQL> alter system dump undo header '_SYSSMU9$'; System altered. SQL> alter system dump datafile 2 block 1350; System altered. SQL> select 2 d.value||'/'||lower(rtrim(i.instance, 3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 4 from ( select p.spid 5 from sys.v$mystat m, 6 sys.v$session s,sys.v$process p 7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 8 ( select t.instance from sys.v$thread t,sys.v$parameter v 9 where v.name = 'thread' and 10 (v.value = 0 or t.thread# = to_number(v.value))) i, 11 ( select value from sys.v$parameter 12 where name = 'user_dump_dest') d 13 / TRACE_FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/admin/jingyong/udump/jingyong_ora_5023.trc
查看跟踪文件信息,看数据块上的信息,ITL事务信息仍然存在:
*** 2013-01-07 10:19:33.032
*** SERVICE NAME:(SYS$USERS) 2013-01-07 10:19:33.031
*** SESSION ID:(140.421) 2013-01-07 10:19:33.031
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601
frmt: 0x02 chkval: 0xf364 type: 0x06=trans data
Block header dump: 0x01000020
Object id on Block? Y
seg/obj: 0xc7cc csc: 0x00.e6ebb itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01d.00000181 0x00800546.0129.18 ---- 3 fsc 0x0002.00000000
0x02 0x0002.010.00000158 0x00800598.013f.26 C--- 0 scn 0x0000.000d4495
xid=0x0009.01d.00000181的事务lck=3
数据块的锁定信息仍然存在:
tab 0, row 5, @0x1d11 tl: 40 fb: --H-FL-- lb: 0x1 cc: 8 col 0: [ 3] c2 4d 63 col 1: [ 5] 42 4c 41 4b 45 col 2: [ 7] 4d 41 4e 41 47 45 52 col 3: [ 3] c2 4f 28 col 4: [ 7] 77 b5 05 01 01 01 01 col 5: [ 2] c2 29 col 6: *NULL* col 7: [ 2] c1 1f tab 0, row 6, @0x1d39 tl: 40 fb: --H-FL-- lb: 0x1 cc: 8 col 0: [ 3] c2 4e 53 col 1: [ 5] 43 4c 41 52 4b col 2: [ 7] 4d 41 4e 41 47 45 52 col 3: [ 3] c2 4f 28 col 4: [ 7] 77 b5 06 09 01 01 01 col 5: [ 2] c2 29 col 6: *NULL* col 7: [ 2] c1 0b tab 0, row 7, @0x1e4c tl: 40 fb: --H-FL-- lb: 0x1 cc: 8 col 0: [ 3] c2 4e 59 col 1: [ 5] 53 43 4f 54 54 col 2: [ 7] 41 4e 41 4c 59 53 54 col 3: [ 3] c2 4c 43 col 4: [ 7] 77 bb 04 13 01 01 01 col 5: [ 2] c2 29 col 6: *NULL* col 7: [ 2] c1 15
再来看回滚段的信息:
*** 2013-01-07 10:20:01.417
********************************************************************************
Undo Segment: _SYSSMU9$ (9)
********************************************************************************
Version: 0x01
FREE BLOCK POOL::
uba: 0x00800546.0129.18 ext: 0xa spc: 0x12ea
uba: 0x00000000.0129.05 ext: 0xa spc: 0x1e08
uba: 0x00000000.0129.42 ext: 0xa spc: 0x73e
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x1d 9 0x00 0x0181 0xffff 0x0000.000e743c 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357525082
事务提交,事务表已经释放。如果此时查询scott.emp表,数据库将产生延迟块清除:
SQL> set autotrace on
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4000 30
7782 CLARK MANAGER 7839 09-JUN-81 4000 10
7788 SCOTT ANALYST 7566 19-APR-87 4000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
注意,在此查询是产生了物理读取和redo,这个redo就是因为延迟块清除导致的,再次查询则不会
产生redo了:
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4000 30
7782 CLARK MANAGER 7839 09-JUN-81 4000 10
7788 SCOTT ANALYST 7566 19-APR-87 4000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
再次转储一下该数据块和回滚段:
SQL> alter system dump datafile 4 block 32; System altered. SQL> alter system dump undo header '_SYSSMU9$'; System altered. SQL> alter system dump datafile 2 block 1350; System altered. SQL> select 2 d.value||'/'||lower(rtrim(i.instance, 3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 4 from ( select p.spid 5 from sys.v$mystat m, 6 sys.v$session s,sys.v$process p 7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 8 ( select t.instance from sys.v$thread t,sys.v$parameter v 9 where v.name = 'thread' and 10 (v.value = 0 or t.thread# = to_number(v.value))) i, 11 ( select value from sys.v$parameter 12 where name = 'user_dump_dest') d 13 / TRACE_FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/admin/jingyong/udump/jingyong_ora_5043.trc
查看跟踪文件,看到此时ITL事务信息已经清除,但是注意,这里的xid和uba信息仍然存在:
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e7560 seq: 0x01 flg: 0x00 tail: 0x75600601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01000020
Object id on Block? Y
seg/obj: 0xc7cc csc: 0x00.e7560 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01d.00000181 0x00800546.0129.18 C--- 0 scn 0x0000.000e743c
0x02 0x0002.010.00000158 0x00800598.013f.26 C--- 0 scn 0x0000.000d4495
其中scn表示提交commit scn,fsc表示快速提交scn
数据块块的锁定位也已经清除了:
tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4d 63
col 1: [ 5] 42 4c 41 4b 45
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 05 01 01 01 01
col 5: [ 2] c2 29
col 6: *NULL*
col 7: [ 2] c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4e 53
col 1: [ 5] 43 4c 41 52 4b
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 06 09 01 01 01
col 5: [ 2] c2 29
col 6: *NULL*
col 7: [ 2] c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4e 59
col 1: [ 5] 53 43 4f 54 54
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 bb 04 13 01 01 01
col 5: [ 2] c2 29
col 6: *NULL*
col 7: [ 2] c1 15
8.提交之后的undo信息
当提交事务之后,回滚段事务表标记该事务为非活动,继续再来看一下回滚段数据块的信息,
看到这里的irb指向了0x28，此前的事务已经不可回滚
******************************************************************************** UNDO BLK: xid: 0x0009.02f.00000181 seq: 0x129 cnt: 0x28 irb: 0x28 icl: 0x0 flg: 0x0000
看一下偏移量列表也已经新增了一条信息0x28 0x0a4c
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1f3c 0x02 0x1ed0 0x03 0x1e54 0x04 0x1de8 0x05 0x1d3c 0x06 0x1c90 0x07 0x1c24 0x08 0x1b78 0x09 0x1acc 0x0a 0x1a20 0x0b 0x1974 0x0c 0x1908 0x0d 0x189c 0x0e 0x17f0 0x0f 0x1784 0x10 0x1718 0x11 0x166c 0x12 0x1600 0x13 0x1594 0x14 0x14e8 0x15 0x147c 0x16 0x13e4 0x17 0x1388 0x18 0x132c 0x19 0x1280 0x1a 0x11d4 0x1b 0x1128 0x1c 0x10ac 0x1d 0x1000 0x1e 0x0f54 0x1f 0x0ee8 0x20 0x0e7c 0x21 0x0e10 0x22 0x0da4 0x23 0x0cf8 0x24 0x0c4c 0x25 0x0ba0 0x26 0x0b24 0x27 0x0ab8 0x28 0x0a4c
再看前镜像0x18 0x132c的信息,仍然存在:
*----------------------------- * Rec #0x18 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004) * Layer: 11 (Row) opc: 1 rci 0x17 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- KDO undo record: KTB Redo op: 0x02 ver: 0x01 op: C uba: 0x00800546.0129.17 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01000020 hdba: 0x0100001b itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191 ncol: 8 nnew: 1 size: 1 col 5: [ 3] c2 1d 33