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

参考资料：http://www.adp-gmbh.ch

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271063/viewspace-1013214/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271063/viewspace-1013214/