Query to identify header file and header block
SQL> select segment_name,header_file,header_block from dba_segments where segment_name='EMP';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ----------- ------------
EMP 4 138
SQL> select segment_name,header_file,header_block from dba_segments where segment_name='EMP';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ----------- ------------
EMP 4 138
Get the dump of the block you need
===================================
SQL> Alter system dump datafile 4 block 138;
System altered.
the above query will produce a block dump in default trace directory [UDUMP] of the db.below is the output.
*** 2015-06-01 08:24:44.708
Start dump data blocks tsn: 4 file#:4 minblk 138 maxblk 138
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777354
BH (0x3d3f55c4) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3d2ee000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28
dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f
hash: [0x3d3f5a64,0x51ab3cd0] lru: [0x3d3f5744,0x3d3f559c]
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.149710],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149710],[sfl: 0x0],[lc: 0x0.0]
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x3d3f59e8) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3d2f8000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28
dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f
hash: [0x3ebfaab8,0x3d3f5640] lru: [0x3d3f5b68,0x3d3f59c0]
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.1496ea],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.1496ea],[sfl: 0x0],[lc: 0x0.0]
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x3ebfaa3c) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3ebba000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28
dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f
hash: [0x3ebfa9e4,0x3d3f5a64] lru: [0x3ebfaa14,0x3ebfabbc]
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.149697],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149697],[sfl: 0x0],[lc: 0x0.0]
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x3ebfa968) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3ebb8000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28
dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f
hash: [0x3ebfad34,0x3ebfaab8] lru: [0x3ebfa798,0x3ebfaae8]
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.149688],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149688],[sfl: 0x0],[lc: 0x0.0]
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x3ebfacb8) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3ebc0000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28
dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f
hash: [0x51ab3cd0,0x3ebfa9e4] lru: [0x3ebfae38,0x3ebfac90]
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.149679],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149679],[sfl: 0x0],[lc: 0x0.0]
flags:
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x0100008a (4/138)
scn: 0x0000.000db7b9 seq: 0x01 flg: 0x04 tail: 0xb7b92301
frmt: 0x02 chkval: 0x8872 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00878200 to 0x0087A200
878200 0000A223 0100008A 000DB7B9 04010000 [#...............]
878210 00008872 00000000 00000000 00000000 [r...............]
878220 00000000 00000001 00000008 00000A9C [................]
878230 00000000 00000004 00000008 0100008C [................]
878240 00000000 00000000 00000000 00000004 [................]
878250 00000000 00000000 00000000 00000000 [................]
878260 00000004 00000008 0100008C 00000000 [................]
878270 00000000 00000000 00000004 01000088 [................]
878280 01000088 00000000 00000000 00000000 [................]
878290 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
8782D0 00000001 00002000 00000000 00001434 [..... ......4...]
8782E0 00000000 01000089 00000001 01000088 [................]
8782F0 01000089 00000000 00000000 00000000 [................]
878300 00000000 00000000 00000001 00000000 [................]
878310 000123F5 10000000 01000088 00000008 [.#..............]
878320 00000000 00000000 00000000 00000000 [................]
Repeat 152 times
878CB0 01000088 0100008B 00000000 00000000 [................]
878CC0 00000000 00000000 00000000 00000000 [................]
Repeat 151 times
879640 00000000 00000000 01000089 00000000 [................]
879650 00000000 00000000 00000000 00000000 [................]
Repeat 185 times
87A1F0 00000000 00000000 00000000 B7B92301 [.............#..]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100008c ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 4
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0100008c ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 4
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01000088
Level 1 BMB for Low HWM block: 0x01000088
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000089
Last Level 1 BMB: 0x01000088
Last Level II BMB: 0x01000089
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 74741 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000088 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000088 Data dba: 0x0100008b
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000089
End dump data blocks tsn: 4 file#: 4 minblk 138 maxblk 138
Simple example to get the table name from the block dump information.
SQL> select name from sys.obj$ where obj#=74741;
NAME
------------------------------
EMP
