ロールバックセグメントノート

10727 ワード

レコードを更新します.

conn scott/admin
update emp set sal=5000 where empno=7788;

トランザクション情報:

select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
--xidusn 
select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat where xacts=1;
select * from v$rollname where usn=&xidusn;

dumpロールバックセグメント:

alter system dump undo header "_SYSSMU1$";
 ：
********************************************************************************
Undo Segment:  _SYSSMU1$ (1)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0      spare2: 0      #extents: 3      #blocks: 143
last map  0x00000000  #maps: 0      offset: 4080
Highwater::  0x00800697  ext#: 2      blk#: 14     ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk  0x00000000  offset: 2
Unlocked
Map Header:: next  0x00000000  #extents: 3    obj#: 0      flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080000a  length: 7
0x008000f9  length: 8
0x00800689  length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0  Commit Time: 1371517425
Extent Number:1  Commit Time: 1371517432
Extent Number:2  Commit Time: 1371513608
TRN CTL:: seq: 0x4411 chd: 0x0006 ctl: 0x000f inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800697.4411.01 scn: 0x0000.05d235b9
Version: 0x01
FREE BLOCK POOL::
uba: 0x00800697.4411.01 ext: 0x2  spc: 0x1f88
uba: 0x00000000.4411.04 ext: 0x2  spc: 0x1e12
uba: 0x00000000.440e.03 ext: 0x3  spc: 0xb94
uba: 0x00000000.4244.01 ext: 0x2  spc: 0x1f88
uba: 0x00000000.2864.01 ext: 0x2  spc: 0x1f88
TRN TBL::
index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x00    9    0x00  0x727d  0x001f  0x0000.05d236f8  0x00800694  0x0000.000.00000000  0x00000001   0x00000000  1371518173
......
0x26   10    0x80  0x727c  0x0002  0x0000.05d23ca1  0x00800696  0x0000.000.00000000  0x00000001   0x00000000  0
0x27    9    0x00  0x7280  0x0002  0x0000.05d237c7  0x00800694  0x0000.000.00000000  0x00000001   0x00000000  1371518320
0x28    9    0x00  0x727c  0x000f  0x0000.05d23ccf  0x00800696  0x0000.000.00000000  0x00000001   0x00000000  1371518881
0x29    9    0x00  0x727b  0x000b  0x0000.05d23859  0x00800695  0x0000.000.00000000  0x00000001   0x00000000  1371518397
0x2a    9    0x00  0x727a  0x0008  0x0000.05d239a2  0x00800695  0x0000.000.00000000  0x00000001   0x00000000  1371518560
0x2b    9    0x00  0x727d  0x000c  0x0000.05d23777  0x00800694  0x0000.000.00000000  0x00000001   0x00000000  1371518254
0x2c    9    0x00  0x727d  0x0023  0x0000.05d236cb  0x00800693  0x0000.000.00000000  0x00000001   0x00000000  1371518156
0x2d    9    0x00  0x727b  0x0018  0x0000.05d23728  0x00800694  0x0000.000.00000000  0x00000001   0x00000000  1371518202
state 10 。
select to_number('26', 'xxxxxxx') from dual;
TO_NUMBER('26','XXXXXX')
------------------------
38
 v$transaction xidslot 。

dumpフロントミラー:
DBA(data block address)の前ミラーアドレスは、0 x 008000696から2進数に変換:0000 0000 1000 0000 0000 0110 1001 0110の前10ビット代表ファイル番号が2、後22ビット代表block番号が1686であり、この前ミラー情報はfile 2 block 1686にあり、トランザクションテーブルクエリの結果は:

SELECT  b.USERNAME,a.xidusn,a.xidslot,a.xidsqn,a.ubablk,a.ubafil,a.ubarec FROM v$transaction a,v$session b where a.SES_ADDR=b.SADDR;
USERNAME    XIDUSN  XIDSLOT XIDSQN  UBABLK  UBAFIL  UBAREC
1   SCOTT       1   38  29308   1686    2   14

前のセッションで更新を続行します.

update emp set sal=5000 where empno=7782;
update emp set sal=5000 where empno=7698;

dump現在のblock:

alter system dump datafile 2 block 1686;
********************************************************************************
UNDO BLK:
xid: 0x0006.028.0000729f  seq: 0x3eac cnt: 0x2d  irb: 0x2d  icl: 0x0   flg: 0x0000
Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f7c     0x02 0x1f38     0x03 0x1ef4     0x04 0x1eb0     0x05 0x1e6c
0x06 0x1d9c     0x07 0x1d18     0x08 0x1c88     0x09 0x1c04     0x0a 0x1b80
0x0b 0x1afc     0x0c 0x1a80     0x0d 0x19b0     0x0e 0x192c     0x0f 0x189c
0x10 0x1818     0x11 0x1794     0x12 0x1710     0x13 0x1694     0x14 0x1610
0x15 0x1594     0x16 0x152c     0x17 0x14c0     0x18 0x143c     0x19 0x13c0
0x1a 0x1370     0x1b 0x1304     0x1c 0x1280     0x1d 0x1204     0x1e 0x11b4
0x1f 0x1148     0x20 0x1084     0x21 0x1000     0x22 0x0f70     0x23 0x0eec
0x24 0x0e68     0x25 0x0de4     0x26 0x0d68     0x27 0x0ce4     0x28 0x0c68
0x29 0x0c18     0x2a 0x0bac     0x2b 0x0b14     0x2c 0x0abc     0x2d 0x0a64
irb:  。
 irb 。 undo chain 0x00,
*-----------------------------
* Rec #0x2b  slt: 0x28  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: 0x00802b2a.3eac.27 ctl max scn: 0x0000.05d75754 prv tx scn: 0x0000.05d7576a
txn start scn: scn: 0x0000.00000000 logon user: 54
prev brb: 8399655 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0008.027.00007072 uba: 0x008000ec.4883.02
flg: C---    lkc:  0     scn: 0x0000.0591e02e
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000020  hdba: 0x0100001b
itli: 2  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]  c2 33
*-----------------------------
* Rec #0x2c  slt: 0x28  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x2b
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: 0x00802b2a.3eac.2b
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000020  hdba: 0x0100001b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 6(0x6) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col  5: [ 2]  c2 33
*-----------------------------
* Rec #0x2d  slt: 0x28  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x2c
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: 0x00802b2a.3eac.2c
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000020  hdba: 0x0100001b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col  5: [ 2]  c2 33
SQL> select utl_raw.cast_to_number('c233') from dual;
UTL_RAW.CAST_TO_NUMBER('C233')
------------------------------
5000

x$bhから取得したデータブロックの使用状況:

SELECT b.segment_name, a.file#, a.dbarfil, a.dbablk, a.class, a.state,decode(bitand(flag,1),0,'N','Y') dirty
FROM x$bh a, dba_extents b
where a.dbarfil = b.relative_fno
and b.block_id <= a.dbablk
and b.block_id + b.blocks > a.dbablk
and b.owner = 'SCOTT'
and b.segment_name = 'EMP';
 ， ， bdba 。bdba: 0x01000020 2 
0000 0001 0000 0000 0000 0000 0000 0010 0000 file 4 block 32

dump file#およびdbablk:

Dump block:
alter system dump datafile 4 block 32;
trace ：
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.026.0000727c  0x00800696.4411.10  C---    0  scn 0x0000.05d44076
0x02   0x0006.028.0000729f  0x00802b2a.3eac.2d  ----    3  fsc 0x0000.00000000
xid  transaction ID[Undo.Segment.Number+Transaction.Table.Slot.Number+Wrap];
Uba  Undo Block Address
Lck  Lock Status
0x0006.028.0000729f  6 ，solt 0x28(10 40)，wrap# 729f, 6 dump ：
index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x28   10    0x80  0x729f  0x0002  0x0000.00000000  0x00802b2a  0x0000.000.00000000  0x00000001   0x00000000  0
Uba(Undo Block Address):0x00802b2a.3eac.2d   0x00802b2a ，seq,3eac ，2d UNDO (irb ）
uba Undo ：
UNDO BLK:
xid: 0x0006.028.0000729f  seq: 0x3eac cnt: 0x2d  irb: 0x2d  icl: 0x0   flg: 0x0000
3 ， LB 0x02 Itl:
tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x2  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: 0x2  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: 0x2  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

eygleの「深入浅出oracle」から抜粋

サードパーティによる遅延評価の実施

【文字列置換】プログラマー面接金典-1.4スペース置換