How to located and dump a data block in oracle

SQL> create table mytest(cl1 number, cl2 varchar2(32));

Table created.


SQL> insert into mytest values (1, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');

1 row created.

SQL> insert into mytest values (2, 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');

1 row created.

SQL> commit;

Commit complete.

SQL> select ora_rowscn scn, dbms_rowid.rowid_block_number(rowid) block from mytest;

       SCN      BLOCK
---------- ----------
 325082188     175177
 325082188     175177

SQL>  select header_file, header_block, blocks from dba_segments where segment_name = 'MYTEST';

HEADER_FILE HEADER_BLOCK     BLOCKS
----------- ------------ ----------
          1       175176          8

SQL> alter session set tracefile_identifier=mytest;

Session altered.

SQL> alter system dump datafile 1 block 175177;

System altered.


Below are the dump from cache, you should be see scn(last change SCN), csc(last clean SCN), itl... etc information in block header
==================================================================

Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4369481
BH (0x7ebc98c0) file#: 1 rdba: 0x0042ac49 (1/175177) class: 1 ba: 0x7ea1a000
  set: 11 bsz: 8192 bsi: 0 sflg: 0 pwc: 0, 25 lid: 0x00000000,0x00000000
  dbwrid: 0 obj: 139983 objn: 139983 tsn: 0 afn: 1
  hash: [0x9de2e2d0,0x9de2e2d0] lru: [0x7dbc7428,0x823cdd38]
  obj-flags: object_ckpt_list
  ckptq: [0x9e78f9a8,0x7dbc73d8] fileq: [0x9e78f9c8,0x9e78f9c8] objq: [0x9a7d9080,0x9a7d9080]
  st: XCURRENT md: NULL tch: 3 le: 0x777e7d58
  flags: buffer_dirty redo_since_read gotten_in_current_mode
  LRBA: [0x49a.237.0] LSCN: [0x0.13605c21] HSCN: [0x0.13605c4c] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
  buffer tsn: 0 rdba: 0x0042ac49 (1/175177)
  scn: 0x0000.13605c4c seq: 0x01 flg: 0x02 tail: 0x5c4c0601
  frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007EA1A000 to 0x000000007EA1C000
07EA1A000 0000A206 0042AC49 13605C4C 02010000  [....I.B.L\`.....]
07EA1A010 00000000 00000001 000222CF 13605C21  [........."..!\`.]
07EA1A020 00000000 00030002 00000000 0008000A  [................]
07EA1A030 00029F08 00C006F0 00128556 00002002  [........V.... ..]
07EA1A040 13605C4C 00000000 00000000 00000000  [L\`.............]
07EA1A050 00000000 00000000 00000000 00020100  [................]
07EA1A060 0016FFFF 1F3C1F52 00001F3C 1F790002  [....R.<.<.....y.]
07EA1A070 00001F52 00000000 00000000 00000000  [R...............]
07EA1A080 00000000 00000000 00000000 00000000  [................]
        Repeat 497 times
07EA1BFA0 00000000 00000000 00000000 012C0000  [..............,.]
07EA1BFB0 03C10202 42424220 42424242 42424242  [.... BBBBBBBBBBB]
07EA1BFC0 42424242 42424242 42424242 42424242  [BBBBBBBBBBBBBBBB]
07EA1BFD0 42424242 02012C42 2002C102 41414141  [BBBBB,..... AAAA]
07EA1BFE0 41414141 41414141 41414141 41414141  [AAAAAAAAAAAAAAAA]
07EA1BFF0 41414141 41414141 41414141 5C4C0601  [AAAAAAAAAAAA..L\]
Block header dump:  0x0042ac49
 Object id on Block? Y
 seg/obj: 0x222cf  csc: 0x00.13605c21  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
Trace file /u02/oracle/diag/DBAPRD/diag/rdbms/dbaprd/DBAPRD1/trace/DBAPRD1_ora_8207_MYTEST.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:    Linux
Node name:      racprod1-lnx
Release:        2.6.18-53.el5
Version:        #1 SMP Wed Oct 10 16:34:19 EDT 2007
Machine:        x86_64
Instance name: DBAPRD1
Redo thread mounted by this instance: 1
Oracle process number: 62
Unix process pid: 8207, image: oracle@racprod1-lnx (TNS V1-V3)


*** 2013-06-08 22:19:47.221
*** SESSION ID:(214.14265) 2013-06-08 22:19:47.221
*** CLIENT ID:() 2013-06-08 22:19:47.221
*** SERVICE NAME:(SYS$USERS) 2013-06-08 22:19:47.221
*** MODULE NAME:(sqlplus@racprod1-lnx (TNS V1-V3)) 2013-06-08 22:19:47.221
*** ACTION NAME:() 2013-06-08 22:19:47.221

Start dump data blocks tsn: 0 file#:1 minblk 175177 maxblk 175177
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4369481
BH (0x7ebc98c0) file#: 1 rdba: 0x0042ac49 (1/175177) class: 1 ba: 0x7ea1a000
  set: 11 bsz: 8192 bsi: 0 sflg: 0 pwc: 0, 25 lid: 0x00000000,0x00000000
  dbwrid: 0 obj: 139983 objn: 139983 tsn: 0 afn: 1
  hash: [0x9de2e2d0,0x9de2e2d0] lru: [0x7dbc7428,0x823cdd38]
  obj-flags: object_ckpt_list
  ckptq: [0x9e78f9a8,0x7dbc73d8] fileq: [0x9e78f9c8,0x9e78f9c8] objq: [0x9a7d9080,0x9a7d9080]
  st: XCURRENT md: NULL tch: 3 le: 0x777e7d58
  flags: buffer_dirty redo_since_read gotten_in_current_mode
  LRBA: [0x49a.237.0] LSCN: [0x0.13605c21] HSCN: [0x0.13605c4c] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
  buffer tsn: 0 rdba: 0x0042ac49 (1/175177)
  scn: 0x0000.13605c4c seq: 0x01 flg: 0x02 tail: 0x5c4c0601
  frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007EA1A000 to 0x000000007EA1C000
07EA1A000 0000A206 0042AC49 13605C4C 02010000  [....I.B.L\`.....]
07EA1A010 00000000 00000001 000222CF 13605C21  [........."..!\`.]
07EA1A020 00000000 00030002 00000000 0008000A  [................]
07EA1A030 00029F08 00C006F0 00128556 00002002  [........V.... ..]
07EA1A040 13605C4C 00000000 00000000 00000000  [L\`.............]
07EA1A050 00000000 00000000 00000000 00020100  [................]
07EA1A060 0016FFFF 1F3C1F52 00001F3C 1F790002  [....R.<.<.....y.]
07EA1A070 00001F52 00000000 00000000 00000000  [R...............]
07EA1A080 00000000 00000000 00000000 00000000  [................]
        Repeat 497 times
07EA1BFA0 00000000 00000000 00000000 012C0000  [..............,.]
07EA1BFB0 03C10202 42424220 42424242 42424242  [.... BBBBBBBBBBB]
07EA1BFC0 42424242 42424242 42424242 42424242  [BBBBBBBBBBBBBBBB]
07EA1BFD0 42424242 02012C42 2002C102 41414141  [BBBBB,..... AAAA]
07EA1BFE0 41414141 41414141 41414141 41414141  [AAAAAAAAAAAAAAAA]
07EA1BFF0 41414141 41414141 41414141 5C4C0601  [AAAAAAAAAAAA..L\]
Block header dump:  0x0042ac49
 Object id on Block? Y
 seg/obj: 0x222cf  csc: 0x00.13605c21  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.008.00029f08  0x00c006f0.8556.12  --U-    2  fsc 0x0000.13605c4c
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0042ac49
data_block_dump,data header at 0x7ea1a05c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x7ea1a05c
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f52
avsp=0x1f3c
tosp=0x1f3c
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f79
0x14:pri[1]     offs=0x1f52
block_row_dump:
tab 0, row 0, @0x1f79
tl: 39 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [32]
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41
tab 0, row 1, @0x1f52
tl: 39 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [32]
 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42
 42 42 42 42 42 42 42
end_of_block_dump