Basics queries on Oracle Block

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

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