DML语句时回滚和重作

测试环境 10.2.0.5 red hat linux 5

1.测试：

创建表及插入相关数据

drop table dun purge;
create table dun(id int,name varchar2(20));
begin
for i in 1..100 loop
insert into dun values(i,'a');
end loop;
end;
/

begin
for i in 101..200 loop
insert into dun values(i,'b');
end loop;
end;
/

begin
for i in 201..300 loop
insert into dun values(i,'c');
end loop;
end;
/
commit;

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1        453   52428800          1 NO  CURRENT                5801344 27-AUG-14
         2          1        451   52428800          1 YES INACTIVE               5780201 25-AUG-14
         3          1        452   52428800          1 YES ACTIVE                 5801129 27-AUG-14

SQL> select le.leseq "Current log sequence No",100 * cp.cpodr_bno / le.lesiz "Percent Full",(cpodr_bno - 1) * 512 "Current Offset",
le.lesiz * 512 - cpodr_bno * 512 "Left space"
from x$kcccp cp, x$kccle le where LE.leseq = CP.cpodr_seq and bitand(le.leflg, 24) = 8;

Current log sequence No Percent Full Current Offset Left space
----------------------- ------------ -------------- ----------
                    456      .203125         105984   52322304

通过以上查询我们可以了解实际的redo写出情况:Current Offset说明了当前日志文件所写到的位置，而Left Space说明了当前日志文件所剩余的空间
105984(Current Offset)+52322304(Left space)+512(redo header)=logfile size=le.lesiz* redo block size=50M

SQL> SELECT distinct dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id FROM dun;

 OBJECT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
     60263          1      10434

查看操作之前的SCN号

select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)
------------------------------------
                             5826361

session A;
update dun set name='aa' where id<=100;
update dun set name='bb' where id>100 and id<=200;
commit;

session B;
update dun set name='aaa' where id<=100;
update dun set name='bbb' where id>100 and id<=200;
update dun set name='cc' where id>200 and id<=300;
commit;

session C:
update dun set name='aaaa' where id<100;
update dun set name='bbbb' where id>100 and id<=200;
update dun set name='ccc' where id>200 and id<=300;

数据更新过程

a->aa->aaa->aaaa
b->bb->bbb->bbbb
c->c->cc->ccc

查看操作之后的SCN号

SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)
------------------------------------
                             5826470

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1        456   52428800          1 NO  CURRENT                5825956 28-AUG-14
         2          1        454   52428800          1 YES INACTIVE               5801496 27-AUG-14
         3          1        455   52428800          1 YES INACTIVE               5804213 27-AUG-14

SQL> select le.leseq "Current log sequence No",100 * cp.cpodr_bno / le.lesiz "Percent Full",(cpodr_bno - 1) * 512 "Current Offset",
le.lesiz * 512 - cpodr_bno * 512 "Left space"
from x$kcccp cp, x$kccle le where LE.leseq = CP.cpodr_seq and bitand(le.leflg, 24) = 8;

Current log sequence No Percent Full Current Offset Left space
----------------------- ------------ -------------- ----------
                    456     .7578125         396800   52031488

SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         3         38        799          2         42        651          8

SQL> select * from v$rollname ;

       USN NAME
---------- ------------------------------
         0 SYSTEM
         1 _SYSSMU1$
         2 _SYSSMU2$
         3 _SYSSMU3$
         4 _SYSSMU4$
         5 _SYSSMU5$
         6 _SYSSMU6$
         7 _SYSSMU7$
         8 _SYSSMU8$
         9 _SYSSMU9$
        10 _SYSSMU10$

（1）分析UNDO文