事务提交（快速提交）对应undo的记录

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

 

 

create table tt (id int,name varchar2(20));

Table created.

insert into tt select empno,ename from demo;
18 rows created.

SQL> select * from tt;

        ID NAME
---------- ---------------------------------------------
      7782 CLARK
      7839 KING
      7934 MILLER
      7369 SMITH
      7566 JONES
      7788 SCOTT
      7876 ADAMS
      7902 FORD
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7698 BLAKE
      7844 TURNER
      7900 JAMES
      1000 TOMMY
      1001 TOMMY
      1002 TOMMY
      1005 TOMMY

18 rows selected.

 

session 1

对id='7782'行进行修改（暂时不要提交）
SQL> update tt set name='AAAA' where id='7782';

1 row updated.

session 2

查看 id='7782'对应的数据文件，数据块。

select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_block_NUMBER(rowid),id,name from tt where id=7782
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)        ID    NAME
------------------------------------ ------------------------------------ ----------  

                     4                                  332                  7782          CLARK

alter system dump datafile 5 blcok 332

 

 session 3

dump 出5号文件的332号块，对应的undo记录。

Block header dump:  0x0100014c
 Object id on Block? Y
 seg/obj: 0x2635  csc: 0x00.2a091  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000149 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
  Itl                                   Xid                  Uba                       Flag   Lck        Scn/Fsc
0x01   0x000a.00d.0000006a  0x00802787.0038.04  C---    0  scn 0x0000.0002a062
0x02   0x0006.00a.0000006b  0x00800b6b.0036.05   ----    1  fsc 0x0001.00000000           

  - ITL是卧槽l中lck 为1 说明事务是活动的，
                                                                                       
 

data_block_dump,data header at 0xeab3464
===============
tsiz: 0x1f98
hsiz: 0x36
pbl: 0x0eab3464
bdba: 0x0100014c
     76543210
flag=--------
ntab=1
nrow=18
frre=-1
fsbo=0x36
fseo=0x1ea4
avsp=0x1e7a
tosp=0x1e7b
0xe:pti[0]      nrow=18 offs=0
0x12:pri[0]     offs=0x1ea4
0x14:pri[1]     offs=0x1ebd
0x16:pri[2]     offs=0x1ec9
0x18:pri[3]     offs=0x1ed7
0x1a:pri[4]     offs=0x1ee4
0x1c:pri[5]     offs=0x1ef1
0x1e:pri[6]     offs=0x1efe
0x20:pri[7]     offs=0x1f0b
0x22:pri[8]     offs=0x1f17
0x24:pri[9]     offs=0x1f24
0x26:pri[10]    offs=0x1f30
0x28:pri[11]    offs=0x1f3e
0x2a:pri[12]    offs=0x1f4b
0x2c:pri[13]    offs=0x1f59
0x2e:pri[14]    offs=0x1f65
0x30:pri[15]    offs=0x1f71
0x32:pri[16]    offs=0x1f7e
0x34:pri[17]    offs=0x1f8b

 

在session 1 进行事务提交 commit；

再次dump对应的数据文件，数据块。
Block header dump:  0x0100014c
 Object id on Block? Y
 seg/obj: 0x2635  csc: 0x00.2a091  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000149 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl                          Xid                                   Uba                Flag  Lck        Scn/Fsc
0x01   0x000a.00d.0000006a  0x00802787.0038.04  C---    0  scn 0x0000.0002a062
0x02   0x0006.00a.0000006b  0x00800b6b.0036.05  --U-    1  fsc 0x0001.0002a164

说明： flag 状态为U，快速提交，，但是lck不会被释放，修改了对应的scn

 

data_block_dump,data header at 0xe96c464
===============
tsiz: 0x1f98
hsiz: 0x36
pbl: 0x0e96c464
bdba: 0x0100014c
     76543210
flag=--------
ntab=1
nrow=18
frre=-1
fsbo=0x36
fseo=0x1ea4
avsp=0x1e7a
tosp=0x1e7b
0xe:pti[0]      nrow=18 offs=0
0x12:pri[0]     offs=0x1ea4
0x14:pri[1]     offs=0x1ebd
0x16:pri[2]     offs=0x1ec9
0x18:pri[3]     offs=0x1ed7
0x1a:pri[4]     offs=0x1ee4
0x1c:pri[5]     offs=0x1ef1
0x1e:pri[6]     offs=0x1efe
0x20:pri[7]     offs=0x1f0b
0x22:pri[8]     offs=0x1f17
0x24:pri[9]     offs=0x1f24
0x26:pri[10]    offs=0x1f30
0x28:pri[11]    offs=0x1f3e
0x2a:pri[12]    offs=0x1f4b
0x2c:pri[13]    offs=0x1f59
0x2e:pri[14]    offs=0x1f65
0x30:pri[15]    offs=0x1f71
0x32:pri[16]    offs=0x1f7e
0x34:pri[17]    offs=0x1f8b
block_row_dump:
tab 0, row 0, @0x1ea4

 可以通过ITL中UBA地址查看该事务的前镜像，通过uba的对应的文件和文件号dump出对应的记录。如果uba中的对应的序列号在dump的文件中找不到，会话就会报ORA--01555的错误