It's possible to force a hexadecimal dump of the block by first setting the event 10289. Then no interpretation takes place (which might be needed to analyze a corrupt block).
Dumping a block
If the second parameter (p_symbolic) is set to false, the block is dumped hexadecimally.
create or replace procedure dump_block_from_rowid(
p_rowid rowid,
p_symbolic boolean := true)
--
-- Requires
-- grant alter system to …
-- in order to execute procedure.
--
is
dump_file varchar2(4000);
begin
if not p_symbolic then -- create hexadecimal dump
execute immediate q'[
alter session set events '10289 trace name context forever, level 1'
]';
end if;
execute immediate '
alter system dump datafile ' ||
dbms_rowid.rowid_relative_fno(p_rowid) || '
block ' ||
dbms_rowid.rowid_block_number(p_rowid);
if not p_symbolic then -- create hexadecimal dump
execute immediate q'[
alter session set events '10289 trace name context off'
]';
end if;
select
value
into
dump_file
from
v$diag_info
where
name = 'Default Trace File';
dbms_output.put_line(' dumped block to: ' || dump_file);
end;
/
Testing the procedure
create table tq84_dump_block_test (
num number,
txt varchar2(20),
dat date
);
insert into tq84_dump_block_test values (1, 'one' , date '2001-01-01');
insert into tq84_dump_block_test values (2, 'two' , date '2002-02-02');
insert into tq84_dump_block_test values (3, 'three', date '2003-03-03');
insert into tq84_dump_block_test values (4, 'four' , date '2004-04-04');
insert into tq84_dump_block_test values (5, 'five' , date '2005-05-05');
commit;
declare
rowid_ rowid;
begin
select
rowid into rowid_
from
tq84_dump_block_test
where
rownum = 1;
dump_block_from_rowid(rowid_, p_symbolic => true);
end;
/
drop table tq84_dump_block_test;
Dumping a segment header block
create table tq84_seg_hdr_dump_test (
col_1 number,
col_2 varchar2(20)
);
insert into tq84_seg_hdr_dump_test values (1, 'one');
insert into tq84_seg_hdr_dump_test values (2, 'two');
commit;
A segment header is just a special type of block and can therefore be dumped with alter system dump … as well.
The block number of the segment header can be queried from
dba_segments:
select
header_file,
header_block
from
dba_segments
where
segment_name = 'TQ84_SEG_HDR_DUMP_TEST';
--
-- HEADER_FILE HEADER_BLOCK
-- ----------- ------------
-- 4 2250
alter system dump datafile 4 block 2250;
After dumping the block, the tracefile is found in
v$diag_info with the following query:
select
value trace_file
from
v$diag_info
where
name = 'Default Trace File';