m_start_block_id number;
m_file_id number;
m_block_id number;
m_process number;
begin
select
xidusn,
start_ubafil,
start_ubablk,
ubafil,
ubablk
into
m_xidusn,
m_start_file_id,
m_start_block_id,
m_file_id,
m_block_id
from
v$session ses,
v$transaction trx
where
ses.sid = (select mys.sid from V$mystat mys where rownum = 1)
and trx.ses_addr = ses.saddr
;
select
file_id, block_id
into
m_header_file_id,
m_header_block_id
from
dba_rollback_segs
where
segment_id = m_xidusn
;
dbms_output.put_line('Header File: ' || m_header_file_id || ' Header block: ' || m_header_block_id);
dbms_output.put_line('Start File: ' || m_start_file_id || ' Start block: ' || m_start_block_id);
dbms_output.put_line('Current File: ' || m_file_id || ' Current block: ' || m_block_id);
dbms_system.ksdwrt(1,'===================');
dbms_system.ksdwrt(1,'Undo Segment Header');
dbms_system.ksdwrt(1,'===================');
execute immediate
'alter system dump datafile ' || m_header_file_id ||' block ' || m_header_block_id;
dbms_system.ksdwrt(1,'================');
dbms_system.ksdwrt(1,'Undo Start block');
dbms_system.ksdwrt(1,'================');
execute immediate
'alter system dump datafile ' || m_start_file_id ||' block ' || m_start_block_id;
if m_start_block_id != m_block_id then
dbms_system.ksdwrt(1,'==================');
dbms_system.ksdwrt(1,'Current Undo block');
dbms_system.ksdwrt(1,'==================');
execute immediate
'alter system dump datafile ' || m_file_id ||' block ' || m_block_id;
end if;
select
spid
into
m_process
from
v$session se,
v$process pr
where se.sid = (select sid from v$mystat where rownum = 1)
and
pr.addr = se.paddr
;
dbms_output.put_line('Trace file name includes: ' || m_process);
end;
/
grant execute on dump_undo_block to public;
drop public synonym dump_undo_block;
create public synonym dump_undo_block for dump_undo_block;
?
以上步骤只是创建了一个表t1，
3.4.2 转储表t1的第一个数据块
execute dump_table_block('t1')
?
3.4.3 另外开启一个会话，将id为1的记录n1的值改为101，然后改为102，然后改为103。并记录块的变化和undo块的变化。
?
select * from t1;--清除标记 alter system checkpoint;--刷新输出磁盘，执行完等5,6s
execute dump_table_block('t1')--转储数据块 序号1 update t1 set n1=101 where id=1;--第一次更新alter system checkpoint;--刷新输出磁盘，执行完等5,6sexecute dump_table_block('t1')--转储数据块execute dump_undo_block--转储undo块序号2update t1 set n1=102 where id=1;--第二次更新alter system checkpoint;--刷新输出磁盘，执行完等5,6sexecute dump_table_block('t1')--转储数据块execute dump_undo_block--转储undo块序号3update t1 set n1=103 where id=1;--第三次更新alter system checkpoint;--刷新输出磁盘，执行完等5,6sexecute dump_table_block('t1')--转储数据块execute dump_undo_block--转储undo块序号4
?
?
3.4.4 找到转储出来的日志文件
序号1 执行完后
?
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.015.0000065c 0x00c009af.01e8.1c --U- 3 fsc 0x0000.0035e07c --理论上，这里的标记应该被清除了
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x00416169
data_block_dump,data header at 0x1ce9705c
标记没有清掉，不知道为什么
序号2 执行完后
数据块转储结果
*** 2015-01-04 22:52:48.506
Start dump data blocks tsn: 0 file#:1 minblk 90473 maxblk 90473
Block dump from cache:
Du