ive_fno,
a.block_id,
a.blocks,
sum(a.blocks) over(
order
by b.data_object_id, a.relative_fno, a.block_id) sum2,
ceil(
sum(a.blocks)
over() / &trunks) chunk_size
from dba_extents a, dba_objects b
where a.
owner = b.
owner
and a.segment_name = b.object_name
and nvl(a.partition_name, '-1') =
nvl(b.subobject_name, '-1')
and b.data_object_id
is
not
null
and a.
owner =
upper('&owner')
and a.segment_name =
upper('&table_name'))))
group
by chunk_no
order
by chunk_no) a);
块延迟清除导致ORA-01555错误示例 （1）新建一个非常小的undo表空间，并切换至改表空间，同时新建两个表dhtest1、dhtest2。
SQL>
create undo tablespace undo2 datafile '/u01/test/test/undo2.dbf'
size 2m autoextend
off;
Tablespace created.
SQL>
alter
system
set undo_tablespace='UNDO2';
System altered.
SQL>
create
table dhtest1
as
select object_id,object_name
from dba_objects;
Table created.
SQL>
create
table dhtest2
as
select
owner,
table_name
from dba_tables;
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,
OWNER,
TABLE_NAME
6
FROM dhtest2
WHERE
table_name='DHTEST1';
OBJECT_ID REL_FNO BLOCKNO ROWNO ROWID
OWNER
TABLE_NAME
---------- ---------- ---------- ---------- ------------------ ------------------------------------------------------------
73431 4 957 83 AAAR7XAAEAAAAO9ABT DH DHTEST1 （2）开始测试，session A 更新表dhtest2上rowid='AAAR7XAAEAAAAO9ABT'的数据行
update dhtest2
set
table_name='yyyyyy'
where rowid='AAAR7XAAEAAAAO9ABT';
1
row updated. （3）dump 被更新数据行的数据块（datafile 4,block 957)
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/test/test/trace/test_ora_26266.trc
SQL>
alter
system flush buffer_cache; --刷新内存，后续提交不会情况数据块中的事务信息
System altered.
SQL>
alter
system dump datafile 4 block 957;
System altered.
Block header dump: 0x010003bd
Object id
on Block? Y
seg/obj: 0x11ed7 csc: 0x00.fbdc3 itc: 3 flg: E typ: 1 -
DATA
brn: 1 bdba: 0x10003b0 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.000fbdc3
0x02 0x0016.013.00000002 0x018000b4.0000.1b ---- 1 fsc 0x0001.00000000 ---lck为1 表示这个事务锁定了该数据块中的1行数据
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010003bd
data_block_dump,
data header
at 0x2b2cf3514a7c
.....省略一部分.......
tab 0,
row 83, @0x4d1
tl: 13 fb: --H-FL-- lb: 0x2 cc: 2 --lb为2，表示这一行被对应itl中的第二个事务槽中的事务更新。
col 0: [ 2] 44 48
col 1: [ 6] 79 79 79 79 79 79 --转换数据可以发现为 yyyyy ，与我们更新的一致
....后面省略............ （4）查询事务的一些基本信息，例如usn,xidslot,xidsqn等等
SQL>
set linesize 200
SQL> col start_scnw format a20
SQL> col start_scnb format a20
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;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC START_SCNW START_SCNB START_SCN
---------- ---------- ---------- ---------- ---------- ---------- -------------------- -------------------- ----------
22 19 2 180 6 27 0 fbdcc 1031628
SQL>
select
name
from v$rollname
where usn=22;
NAME
-------------------------- |