oracle dump block

如何匯出資料塊的內容？

-- Dumping a block in Oracle

conn scott/tiger

create table dump_table (
a varchar2(4000),
b varchar2(4000),
c varchar2(4000),
d varchar2(4000));
insert into dump_table values(
'aaaaaaaaaa','bbbbbbbbbb','cccccccccc','dddddddddd');
insert into dump_table values(
'AAAAAAAAAA','BBBBBBBBBB','CCCCCCCCCC','DDDDDDDDDD');
commit;

-- Dumping the Segment's Header Block
select header_file, header_block from dba_segments
where segment_name = 'DUMP_TABLE';

HEADER_FILE HEADER_BLOCK
----------- ------------
5 185987

alter system dump datafile 5 block 185987;

[@more@]

在 C:oracleadminorcl2udump 目錄產生一個日誌檔案orcl2_ora_6048.trc

-- 日誌檔案在哪裡？
select value from v$parameter where name = 'user_dump_dest';
select spid
from v$session s, v$process p
where p.addr = s.paddr
and s.audsid = sys_context('userenv','sessionid');

-- 對資料塊進行dump
-- Dumping a block from a rowid
使用sys，建立以下過程
create or replace procedure dump_block_from_rowid(p_rowid rowid)
is
dump_file varchar2(4000);
begin
execute immediate '
alter system dump datafile ' ||
dbms_rowid.rowid_relative_fno(p_rowid) || '
block ' ||
dbms_rowid.rowid_block_number(p_rowid);
select
u_dump.value || '/' || instance.value || '_ora_' || v$process.spid || '.trc'
into
dump_file
from
v$parameter u_dump
cross join v$parameter instance
cross join v$process
join v$session
on v$process.addr = v$session.paddr
where
u_dump.name = 'user_dump_dest' and
instance.name = 'instance_name' and
v$session.audsid=sys_context('userenv','sessionid');

dbms_output.put_line(' dumped block to: ');
dbms_output.put_line(' ' || dump_file);
end;

使用SYS使用者，DUMP出SCOTT使用者的表中的一行
SQL> select rowid from scott.dump_table;

ROWID
------------------
AAABuSAAFAAAtaFAAA
AAABuSAAFAAAtaFAAB

SQL> exec dump_block_from_rowid('AAABuSAAFAAAtaFAAB');

再去檢視日誌檔案，可以發現類似以下內容：
tab 0, row 0, @0x1f69
tl: 47 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [10] 61 61 61 61 61 61 61 61 61 61
col 1: [10] 62 62 62 62 62 62 62 62 62 62
col 2: [10] 63 63 63 63 63 63 63 63 63 63
col 3: [10] 64 64 64 64 64 64 64 64 64 64

參考資料：