原文地址:http://blog.csdn.net/sh231708/article/details/52935695
这篇文章算是undo相关问题总结的补充，因为ORA-01555错误与undo有着直接的关系。
ORA-01555错误是一种在oracle数据库中非常常见的错误，甚至也可以说是一个非常经典的错误，只是由于oracle的发展越来越自动化（UNDO自动管理+加强），这个错误已经越来越少见，可能很多使用10g的DBA都没有遇到过这个错误。
这个错误在9i之前的版本（UNDO手工管理）出现的最多，也是最常见的，甚至可以说怎么样处理和避免ORA-01555 错误是令每一个DBA曾头痛，但是又必须面对的问题。从9i的undo自动管理，至现在的10g、11g中的undo auto tuning，使得ORA-01555错误越来越少，但是这个错误仍然不可避免，特别是那些分析型的系统中（OLTP）。
错误原因（一般有两种）
- SQL语句执行时，需要读取UNDO（前映像数据）来构造CR数据块，完成一致性读取。但是在读取undo前映像数据时发现，undo信息已经被覆盖（undo空间循环使用），不能构造一致性读的CR块，抛出ORA-01555错误
- SQL语句执行时，访问到的数据块，需要进行延迟块清除，但是在进行延迟块清除时，不能确定这个数据块的事务提交时间与SQL执行开始时间的先后次序，从而抛出ORA-01555错误
备注：延迟块清除是指前一个事务完成提交时(commit)，由于修改块已经刷新至磁盘等原因，未完成块事务信息的清除（ILT，LB信息等等），在后续的SQL语句访问该块时，需要清除这些信息，这个动作即延迟块清除。
第一种情况的解决方法（仅供参考）
- 增加UNDO空间，延缓UNDO信息被覆盖，也可以理解为增加undo空间循环使用一次的时间。
- 优化抛出错误的SQL语句，缩短SQL语句执行的时间，也可以避免语句需要访问undo信息被覆盖。
- 避免频繁的提交也是一种可行方法，不过需要改动的量较前两个都要大。提交频率降低后，导致undo 信息被覆盖的可能性也降低了（oracle数据库中未提交的undo是不可能被覆盖），或者undo 事务表被覆盖的可能性也降低了。
备注：针对第二中情况的解决方法，我会在下一篇文章中介绍和模拟案例。
第一种情况示例
（1）新建一个非常小的undo表空间，并设置为不可扩展，将自动管理的undo空间切换至这个空间
SQL> create undo tablespace undo1 datafile '/u01/test/test/undo1.dbf' size 2m autoextend off;
Tablespace created.
SQL> ALTER SYSTEM SET undo_tablespace='UNDO1';
System altered.
Tablespace created.
SQL> ALTER SYSTEM SET undo_tablespace='UNDO1';
System altered.
（2）session 1中构建示例表和选取示例行
SQL> conn dh/dh
Connected.
SQL> create table test as select object_id,object_name from dba_objects;
Table created.
SQL> SELECT
2 dbms_rowid.rowid_object(rowid) object_id,
3 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
4 dbms_rowid.rowid_block_number(rowid) BLOCKNO,
5 dbms_rowid.rowid_row_number(rowid) ROWNO,rowid,object_id,object_name
6 FROM test WHERE object_name='EMP';
OBJECT_ID REL_FNO BLOCKNO ROWNO ROWID OBJECT_ID
---------- ---------- ---------- ---------- ------------------ ----------
OBJECT_NAME
--------------------
73424 4 611 201 AAAR7QAAEAAAAJjADJ 73201
EMP
SQL> select * from test where rowid='AAAR7QAAEAAAAJjADJ';
OBJECT_ID OBJECT_NAME
---------- --------------------
73201 EMP
Connected.
SQL> create table test as select object_id,object_name from dba_objects;
Table created.
SQL> SELECT
2 dbms_rowid.rowid_object(rowid) object_id,
3 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
4 dbms_rowid.rowid_block_number(rowid) BLOCKNO,
5 dbms_rowid.rowid_row_number(rowid) ROWNO,rowid,object_id,object_name
6 FROM test WHERE object_name='EMP';
OBJECT_ID REL_FNO BLOCKNO ROWNO ROWID OBJECT_ID
---------- ---------- ---------- ---------- ------------------ ----------
OBJECT_NAME
--------------------
73424 4 611 201 AAAR7QAAEAAAAJjADJ 73201
EMP
SQL> select * from test where rowid='AAAR7QAAEAAAAJjADJ';
OBJECT_ID OBJECT_NAME
---------- --------------------
73201 EMP
（3）dump出待修改的块，可以看到当前块上没有事务存在
alter system dump datafile 4 block 611;
Block header dump: 0x01000263
Object id on Block? Y
seg/obj: 0x11ed0 csc: 0x00.f3559 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000201 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
0x0000.000f3559
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
Block header dump: 0x01000263
Object id on Block? Y
seg/obj: 0x11ed0 csc: 0x00.f3559 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000201 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
0x0000.000f3559
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
（4）在session 2中发起游标查询
SQL> var cur1 refcursor
SQL> begin
2 open :cur1 for select * from test where rowid='AAAR7QAAEAAAAJjADJ';
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 open :cur1 for select * from test where rowid='AAAR7QAAEAAAAJjADJ';
3 end;
4 /
PL/SQL procedure successfully completed.
（5）在session 1中更新这一行，根据我们知道oracle的读一致性机制，在（4）步骤中‘查询游标’肯定需要回滚下面这一步进行的更新！
SQL> update test set object_name='DH' where rowid='AAAR7QAAEAAAAJjADJ';--dump块时由于没有刷新到磁盘，dump结果没有变化，因此这个地方更新了两次
1 row updated.
SQL> select * from test where rowid='AAAR7QAAEAAAAJjADJ';
OBJECT_ID OBJECT_NAME
---------- --------------------
73201 DH
SQL> commit;
Commit complete.
SQL> update test set object_name='DH' where rowid='AAAR7QAAEAAAAJjADJ';
------第二次更新，实验以此为准
1 row updated.
1 row updated.
SQL> select * from test where rowid='AAAR7QAAEAAAAJjADJ';
OBJECT_ID OBJECT_NAME
---------- --------------------
73201 DH
SQL> commit;
Commit complete.
SQL> update test set object_name='DH' where rowid='AAAR7QAAEAAAAJjADJ';
------第二次更新，实验以此为准
1 row updated.
（6）dump这个块，发现确实存在事务在4,611块上。
alter system dump datafile 4 block 611;
Block header dump: 0x01000263
Object id on Block? Y
seg/obj: 0x11ed0 csc: 0x00.f3a21 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000201 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
0x0000.000f3559
0x02 0x0010.001.00000004 0x014000d4.0001.2b C---
0 scn 0x0000.000f3a01
0x03 0x0012.003.00000005 0x014000f4.0001.01 ----
1 fsc 0x0000.00000000
bdba: 0x01000263
A transaction identifier consists of:
–Undo segment number
–Transaction table slot number
–Sequence number or wrap#
XID = usn# . slot# . wrap#
A UBA consists of:
–Data block address (DBA) of the
block
–The sequence number of the
block
–The record number within the block
UBA = DBA. seq#. rec#
SQL> select dbms_utility.data_block_address_file(to_number(substr(uba,3,8),'xxxxxxxxxxx'))
undo_file#,
2 dbms_utility.data_block_address_block(to_number(substr(uba,3,8),'xxxxxxxxxxx'))
blockno#,
3 to_number(substr(uba,12,4),'xxxxxxxxx') undo_seq,
4 to_number(substr(uba,17,2),'xxxxxxxxx') undo_record
5 from (select '0x014000f4.0001.01' uba from dual);
UNDO_FILE# BLOCKNO# UNDO_SEQ UNDO_RECORD
---------- ---------- ---------- -----------
5 244 1 1
tab 0, row 201, @0x4e7
tl: 11 fb: --H-FL-- lb:
0x3 cc: 2 --lb:0x3 对应上面的第三个itl事务槽，表示这一被锁定，即session 1中的更新操作导致
col 0: [ 4] c3 08 21 02
col
Block header dump: 0x01000263
Object id on Block? Y
seg/obj: 0x11ed0 csc: 0x00.f3a21 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000201 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
0x0000.000f3559
0x02 0x0010.001.00000004 0x014000d4.0001.2b C---
0 scn 0x0000.000f3a01
0x03 0x0012.003.00000005 0x014000f4.0001.01 ----
1 fsc 0x0000.00000000
bdba: 0x01000263
A transaction identifier consists of:
–Undo segment number
–Transaction table slot number
–Sequence number or wrap#
XID = usn# . slot# . wrap#
A UBA consists of:
–Data block address (DBA) of the
block
–The sequence number of the
block
–The record number within the block
UBA = DBA. seq#. rec#
SQL> select dbms_utility.data_block_address_file(to_number(substr(uba,3,8),'xxxxxxxxxxx'))
undo_file#,
2 dbms_utility.data_block_address_block(to_number(substr(uba,3,8),'xxxxxxxxxxx'))
blockno#,
3 to_number(substr(uba,12,4),'xxxxxxxxx') undo_seq,
4 to_number(substr(uba,17,2),'xxxxxxxxx') undo_record
5 from (select '0x014000f4.0001.01' uba from dual);
UNDO_FILE# BLOCKNO# UNDO_SEQ UNDO_RECORD
---------- ---------- ---------- -----------
5 244 1 1
tab 0, row 201, @0x4e7
tl: 11 fb: --H-FL-- lb:
0x3 cc: 2 --lb:0x3 对应上面的第三个itl事务槽，表示这一被锁定，即session 1中的更新操作导致
col 0: [ 4] c3 08 21 02
col
（7）查看session 1更新的事务信息
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec,to_char(start_scnw,'xxxxxxxx')
start_scnw,
2 to_char(start_scnb,'xxxxxxxx') start_scnb, start_scnb+start_scnw*power(2,32)
start_scn
3 from v$transaction;
start_scnw,
2 to_char(start_scnb,'xxxxxxxx') start_scnb, start_scnb+start_scnw*power(2,32)
start_scn
3 from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC START_SCNW START_SCNB START_SCN
---------- - ---------
---------- ---------- -
---------
----------
---------- ----------
---------
18 3 5 244 5
1 0 f3a22 997922
可以看到xidusn对应xid中的0x0012（十六进制转换为十进制后为18），xidslot对应xid中的003，xidsqn对应xid中的00000005
uba中的5、244、1分别对应UBAFIL、UBABLK、UBAREC
（8）dump session 1事务使用的回滚段头和使用回滚段块
TRN CTL:: seq: 0x0001 chd: 0x0004 ctl: 0x0002 inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x014000f4.0001.01 scn: 0x0000.000f36b8 -----0x014000f4.0001.01对应数据块中ITL中的UBA
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0001.26 ext: 0x0 spc: 0xc6a
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
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 0x0005 0x0001 0x0000.000f39c4 0x014000f3 0x0000.000.00000000 0x00000001 0x00000000 1399878011
0x01 9 0x00 0x0005 0x0002 0x0000.000f39ce 0x014000f3 0x0000.000.00000000 0x00000001 0x00000000 1399878011
0x02 9 0x00 0x0005 0xffff 0x0000.000f39d7 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1399878011
0x03 10 0x80 0x0005 0x0000 0x0000.000f3a22 0x014000f4 0x0000.000.00000000 0x00000001 0x00000000 0
0x04 9 0x00 0x0004 0x0005 0x0000.000f3722 0x014000f2 0x0000.000.00000000 0x00000001 0x00000000 1399876988
0x05 9 0x00 0x0004 0x0006 0x0000.000f372d 0x014000f2 0x0000.000.00000000 0x00000001 0x00000000 1399876988
0x06 9 0x00 0x0004 0x0007 0x0000.000f3736 0x014000f2 0x0000.000.00000000 0x00000001 0x00000000 1399876988
....后面的省略.....
UNDO BLK:
xid: 0x0012.003.00000005 seq: 0x1 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset
Rec Offset
---------------------------------------------------------------------------
0x01 0x1f64 *-----------------------------* Rec #0x1 slt: 0x03 objn: 73424(0x00011ed0) objd: 73424 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: 0x00000000Ext idx: 0
flg2: 0*-----------------------------
uba: 0x014000f3.0001.27 ctl max scn: 0x0000.000f35fb prv tx scn: 0x0000.000f36b8
txn start scn: scn: 0x0000.000f3a22 logon user: 85
prev brb: 20971762 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000263 hdba: 0x010000aa
itli: 3 ispac: 0 maxfr: 4858
tabn: 0 slot: 201(0xc9) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
Vector content:
col 1: [ 2] 44 48 ---根据下面的查询，可以知道保存的前映像数据为‘DH'，与我们实验相符SQL> SELECT utl_raw.cast_to_varchar2('4448') from dual;
UTL_RAW.CAST_TO_VARCHAR2('4448')--------------------------------------------------------------------------------
DH
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x014000f4.0001.01 scn: 0x0000.000f36b8 -----0x014000f4.0001.01对应数据块中ITL中的UBA
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0001.26 ext: 0x0 spc: 0xc6a
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
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 0x0005 0x0001 0x0000.000f39c4 0x014000f3 0x0000.000.00000000 0x00000001 0x00000000 1399878011
0x01 9 0x00 0x0005 0x0002 0x0000.000f39ce 0x014000f3 0x0000.000.00000000 0x00000001 0x00000000 1399878011
0x02 9 0x00 0x0005 0xffff 0x0000.000f39d7 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1399878011
0x03 10 0x80 0x0005 0x0000 0x0000.000f3a22 0x014000f4 0x0000.000.00000000 0x00000001 0x00000000 0
0x04 9 0x00 0x0004 0x0005 0x0000.000f3722 0x014000f2 0x0000.000.00000000 0x00000001 0x00000000 1399876988
0x05 9 0x00 0x0004 0x0006 0x0000.000f372d 0x014000f2 0x0000.000.00000000 0x00000001 0x00000000 1399876988
0x06 9 0x00 0x0004 0x0007 0x0000.000f3736 0x014000f2 0x0000.000.00000000 0x00000001 0x00000000 1399876988
....后面的省略.....
UNDO BLK:
xid: 0x0012.003.00000005 seq: 0x1 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset
Rec Offset
---------------------------------------------------------------------------
0x01 0x1f64 *-----------------------------* Rec #0x1 slt: 0x03 objn: 73424(0x00011ed0) objd: 73424 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: 0x00000000Ext idx: 0
flg2: 0*-----------------------------
uba: 0x014000f3.0001.27 ctl max scn: 0x0000.000f35fb prv tx scn: 0x0000.000f36b8
txn start scn: scn: 0x0000.000f3a22 logon user: 85
prev brb: 20971762 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000263 hdba: 0x010000aa
itli: 3 ispac: 0 maxfr: 4858
tabn: 0 slot: 201(0xc9) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
Vector content:
col 1: [ 2] 44 48 ---根据下面的查询，可以知道保存的前映像数据为‘DH'，与我们实验相符SQL> SELECT utl_raw.cast_to_varchar2('4448') from dual;
UTL_RAW.CAST_TO_VARCHAR2('4448')--------------------------------------------------------------------------------
DH
（9）将session 1中的事务提交，同时发现大量的更新，覆盖前面事务使用的回滚段信息
SQL> commit;
Commit complete.
SQL> create table test1 as select object_id,
object_type from dba_objects;
Table created.
SQL> update test1 set object_type='AAAAAAAAAA' where rownum<100;
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
update test1 set object_type='AAAAAAAAAA' where rownum<100
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo
tablespace 'UNDO1'
SQL> begin
2 for i in 1..10000 loop
3 update test1 set object_type='xxxxxx' where rownum<3;
4 commit;
5 end loop;
6 end;
7 /
Commit complete.
SQL> create table test1 as select object_id,
object_type from dba_objects;
Table created.
SQL> update test1 set object_type='AAAAAAAAAA' where rownum<100;
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
99 rows updated.
SQL> /
update test1 set object_type='AAAAAAAAAA' where rownum<100
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo
tablespace 'UNDO1'
SQL> begin
2 for i in 1..10000 loop
3 update test1 set object_type='xxxxxx' where rownum<3;
4 commit;
5 end loop;
6 end;
7 /
（10）再次dump 4,611数据块，发现事务确实已经提交，该块上已经不存在活动事务
Block header dump: 0x01000263
Object id on Block? Y
seg/obj: 0x11ed0 csc: 0x00.f4247 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000201 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
0x0000.000f3559
0x02 0x0010.001.00000004 0x014000d4.0001.2b C---
0 scn 0x0000.000f3a01
0x03 0x0012.003.00000005 0x014000f4.0001.01 C---
0 scn 0x0000.000f407e
bdba: 0x01000263
Object id on Block? Y
seg/obj: 0x11ed0 csc: 0x00.f4247 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000201 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
0x0000.000f3559
0x02 0x0010.001.00000004 0x014000d4.0001.2b C---
0 scn 0x0000.000f3a01
0x03 0x0012.003.00000005 0x014000f4.0001.01 C---
0 scn 0x0000.000f407e
bdba: 0x01000263
（11）再次dump前面session 1第一个事务使用过的回滚段数据块，我们发现undo信息确实已经被覆盖
UNDO BLK:
xid: 0x0012.001.0000001f seq: 0x7 cnt: 0x3f irb: 0x3f icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset
Rec Offset
---------------------------------------------------------------------------
0x01 0x1f60 0x02 0x1f1c 0x03 0x1e74 0x04 0x1dec 0x05 0x1da8
0x06 0x1d00 0x07 0x1c78 0x08 0x1c34 0x09 0x1b8c 0x0a 0x1b04
0x0b 0x1ac0 0x0c 0x1a18 0x0d 0x1990 0x0e 0x194c 0x0f 0x18a4
0x10 0x181c 0x11 0x17d8 0x12 0x1730 0x13 0x16a8 0x14 0x1664
0x15 0x15bc 0x16 0x1534 0x17 0x14f0 0x18 0x1448 0x19 0x13c0
0x1a 0x137c 0x1b 0x12d4 0x1c 0x124c 0x1d 0x1208 0x1e 0x1160
0x1f 0x10d8 0x20 0x1094 0x21 0x0fec 0x22 0x0f64 0x23 0x0f20
0x24 0x0e78 0x25 0x0df0 0x26 0x0dac 0x27 0x0d04 0x28 0x0c7c
0x29 0x0c38 0x2a 0x0b90 0x2b 0x0b08 0x2c 0x0ac4 0x2d 0x0a1c
0x2e 0x0994 0x2f 0x0950 0x30 0x08a8 0x31 0x0820 0x32 0x07dc
0x33 0x0734 0x34 0x06ac 0x35 0x0668 0x36 0x05c0 0x37 0x0538
0x38 0x04f4 0x39 0x044c 0x3a 0x03c4 0x3b 0x0380 0x3c 0x02d8
0x3d 0x0250 0x3e 0x020c 0x3f 0x0164
*-----------------------------
* Rec #0x1 slt: 0x0f objn: 73428(0x00011ed4) objd: 73428 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: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x014000f3.0007.31 ctl max scn: 0x0000.000f9d10 prv tx scn: 0x0000.000f9d31
txn start scn: scn: 0x0000.000f9f44 logon user: 85
prev brb: 20971762 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0012.00e.0000001e uba: 0x014000f3.0007.33
flg: C--- lkc: 0
scn: 0x0000.000f9f3c
KDO Op code: LKR row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100012b hdba: 0x0100012a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0 to: 0 ---已经不存在session 1中第一个事务的undo信息
xid: 0x0012.001.0000001f seq: 0x7 cnt: 0x3f irb: 0x3f icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset
Rec Offset
---------------------------------------------------------------------------
0x01 0x1f60 0x02 0x1f1c 0x03 0x1e74 0x04 0x1dec 0x05 0x1da8
0x06 0x1d00 0x07 0x1c78 0x08 0x1c34 0x09 0x1b8c 0x0a 0x1b04
0x0b 0x1ac0 0x0c 0x1a18 0x0d 0x1990 0x0e 0x194c 0x0f 0x18a4
0x10 0x181c 0x11 0x17d8 0x12 0x1730 0x13 0x16a8 0x14 0x1664
0x15 0x15bc 0x16 0x1534 0x17 0x14f0 0x18 0x1448 0x19 0x13c0
0x1a 0x137c 0x1b 0x12d4 0x1c 0x124c 0x1d 0x1208 0x1e 0x1160
0x1f 0x10d8 0x20 0x1094 0x21 0x0fec 0x22 0x0f64 0x23 0x0f20
0x24 0x0e78 0x25 0x0df0 0x26 0x0dac 0x27 0x0d04 0x28 0x0c7c
0x29 0x0c38 0x2a 0x0b90 0x2b 0x0b08 0x2c 0x0ac4 0x2d 0x0a1c
0x2e 0x0994 0x2f 0x0950 0x30 0x08a8 0x31 0x0820 0x32 0x07dc
0x33 0x0734 0x34 0x06ac 0x35 0x0668 0x36 0x05c0 0x37 0x0538
0x38 0x04f4 0x39 0x044c 0x3a 0x03c4 0x3b 0x0380 0x3c 0x02d8
0x3d 0x0250 0x3e 0x020c 0x3f 0x0164
*-----------------------------
* Rec #0x1 slt: 0x0f objn: 73428(0x00011ed4) objd: 73428 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: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x014000f3.0007.31 ctl max scn: 0x0000.000f9d10 prv tx scn: 0x0000.000f9d31
txn start scn: scn: 0x0000.000f9f44 logon user: 85
prev brb: 20971762 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0012.00e.0000001e uba: 0x014000f3.0007.33
flg: C--- lkc: 0
scn: 0x0000.000f9f3c
KDO Op code: LKR row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100012b hdba: 0x0100012a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0 to: 0 ---已经不存在session 1中第一个事务的undo信息
（12）查询session 2中的游标，我们知道为了读一致性，session 2中的查询肯定需要使用session 1中第一个事务的undo信息，但是目前undo信息已经被覆盖，此时oracle抛出ORA-01555才错误
SQL> print :cur1
ERROR:
ORA-01555: snapshot too old: rollback segment number 18 with name "_SYSSMU18$"
too small
ERROR:
ORA-01555: snapshot too old: rollback segment number 18 with name "_SYSSMU18$"
too small
备注：dump undo段头和undo数据块的语法
select name from v$rollname where usn=18;
alter system dump undo header '_SYSSMU18$';
alter system dump datafile 5 block 244; 或者 alter system dump
undo block '_SYSSMU18$' xid 18 3 5;
alter system dump undo header '_SYSSMU18$';
alter system dump datafile 5 block 244; 或者 alter system dump
undo block '_SYSSMU18$' xid 18 3 5;