Oracle Heap Block Dump

  1. Setup
  2. The block dump

Setup

CREATE TABLE simple_numbers (
  id        NUMBER,
  payload   VARCHAR2(20)
);

INSERT INTO simple_numbers VALUES (0, 'Zero');
INSERT INTO simple_numbers VALUES (1, 'One');
INSERT INTO simple_numbers VALUES (2, 'Two');
INSERT INTO simple_numbers VALUES (10, 'Ten');
INSERT INTO simple_numbers VALUES (99, 'Ninety nine');
INSERT INTO simple_numbers VALUES (100, 'One hundred');
INSERT INTO simple_numbers VALUES (101, 'One hundred and one');

COMMIT;

SELECT *
FROM   simple_numbers;

SELECT * FROM v$instance;

ALTER SESSION SET tracefile_identifier = 'simple_numbers_table';

SELECT dbms_rowid.rowid_relative_fno(rowid) fno,
       dbms_rowid.rowid_block_number(rowid) blockno
FROM   simple_numbers;

SELECT *
FROM   dba_data_files;

alter system checkpoint;

alter system dump datafile 6 block min 11413 block max 11413;

show parameter dump;

The block dump

Trace file /app/oracle/diag/rdbms/ora12nc/ora12nc/trace/ora12nc_ora_2580_simple_numbers_table.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      oranc
Release:        2.6.32-754.6.3.el6.x86_64
Version:        #1 SMP Tue Oct 9 17:27:49 UTC 2018
Machine:        x86_64
Instance name: ora12nc
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 2580, image: oracle@oranc


*** 2018-11-18 20:12:17.972
*** SESSION ID:(265.7) 2018-11-18 20:12:17.972
*** CLIENT ID:() 2018-11-18 20:12:17.972
*** SERVICE NAME:(SYS$USERS) 2018-11-18 20:12:17.972
*** MODULE NAME:(SQL Developer) 2018-11-18 20:12:17.972
*** ACTION NAME:() 2018-11-18 20:12:17.972

Start dump data blocks tsn: 4 file#:6 minblk 11413 maxblk 11413
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=4 rdba=25177237
BH (0x99f6ba58) file#: 6 rdba: 0x01802c95 (6/11413) class: 1 ba: 0x9912c000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 74,28
  dbwrid: 0 obj: 93266 objn: 93266 tsn: [0/4] afn: 6 hint: f
  hash: [0x9bf6fc10,0xa750cd48] lru: [0x99f6bc88,0x99f6ba08]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x99f6ba30,0x7e10e650] objaq: [0x99f6bcc0,0x99f6ba40]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x0.20fa90 tch: 7
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
BH (0x9bf6fb58) file#: 6 rdba: 0x01802c95 (6/11413) class: 1 ba: 0x9b194000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 74,28
  dbwrid: 0 obj: 93256 objn: 93256 tsn: [0/4] afn: 6 hint: f
  hash: [0x9bf9be50,0x99f6bb10] lru: [0x9bf6fb08,0x9cfb5c48]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL]
  objq: [NULL] objaq: [NULL]
  st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.20d865 tch: 0 lfb: 33
  flags:
BH (0x9bf9bd98) file#: 6 rdba: 0x01802c95 (6/11413) class: 1 ba: 0x9b5fe000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 74,28
  dbwrid: 0 obj: 93252 objn: 93252 tsn: [0/4] afn: 6 hint: f
  hash: [0x9cfafe50,0x9bf6fc10] lru: [0x9bf9bd48,0x9bf9bfc8]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL]
  objq: [NULL] objaq: [NULL]
  st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.20d25a tch: 0 lfb: 33
  flags:
BH (0x9cfafd98) file#: 6 rdba: 0x01802c95 (6/11413) class: 1 ba: 0x9c7fe000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 74,28
  dbwrid: 0 obj: 93241 objn: 93241 tsn: [0/4] afn: 6 hint: f
  hash: [0x9cf6a0d0,0x9bf9be50] lru: [0x9cfafd48,0x9cfaffc8]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL]
  objq: [NULL] objaq: [NULL]
  st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.20cfd3 tch: 0 lfb: 33
  flags:
BH (0x9cf6a018) file#: 6 rdba: 0x01802c95 (6/11413) class: 1 ba: 0x9c102000
  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 77,28
  dbwrid: 0 obj: 93240 objn: 93240 tsn: [0/4] afn: 6 hint: f
  hash: [0x9cfb64d0,0x9cfafe50] lru: [0x9cf69fc8,0x9dfd48c8]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL]
  objq: [NULL] objaq: [NULL]
  st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.20cb4f tch: 0 lfb: 33
  flags:
BH (0x9cfb6418) file#: 6 rdba: 0x01802c95 (6/11413) class: 1 ba: 0x9c8a2000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 74,28
  dbwrid: 0 obj: 93239 objn: 93239 tsn: [0/4] afn: 6 hint: f
  hash: [0x95fd67d0,0x9cf6a0d0] lru: [0x9cfb63c8,0x9cfb6648]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL]
  objq: [NULL] objaq: [NULL]
  st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.20cb26 tch: 0 lfb: 33
  flags:
BH (0x95fd6718) file#: 6 rdba: 0x01802c95 (6/11413) class: 1 ba: 0x95bda000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 74,28
  dbwrid: 0 obj: 93238 objn: 93238 tsn: [0/4] afn: 6 hint: f
  hash: [0xa750cd48,0x9cfb64d0] lru: [0x9df99048,0x94f68d08]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL]
  objq: [NULL] objaq: [NULL]
  st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.20cad0 tch: 0 lfb: 33
  flags:
Block dump from disk:
buffer tsn: 4 rdba: 0x01802c95 (6/11413)
scn: 0x0.20fe1b seq: 0x02 flg: 0x06 tail: 0xfe1b0602
frmt: 0x02 chkval: 0x8fd2 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F612E930400 to 0x00007F612E932400
7F612E930400 0000A206 01802C95 0020FE1B 06020000  [.....,.... .....]
7F612E930410 00008FD2 00000001 00016C52 0020FE1A  [........Rl.... .]
7F612E930420 00000000 00320002 01802C90 00110006  [......2..,......]
7F612E930430 00000732 010021D0 000A00E5 00002001  [2....!....... ..]
7F612E930440 0020FE1B 00180007 000005BE 01001153  [.. .........S...]
7F612E930450 001900F8 00008000 0020FE04 00000000  [.......... .....]
7F612E930460 00000000 00070100 0020FFFF 1F111F31  [.......... .1...]
7F612E930470 00001F11 1F8E0007 1F7A1F84 1F5E1F70  [..........z.p.^.]
7F612E930480 1F311F4C 00000000 00000000 00000000  [L.1.............]
7F612E930490 00000000 00000000 00000000 00000000  [................]
        Repeat 495 times
7F612E932390 00000000 02012C00 0202C203 656E4F13  [.....,.......One]
7F612E9323A0 6E756820 64657264 646E6120 656E6F20  [ hundred and one]
7F612E9323B0 0202002C 4F0B02C2 6820656E 72646E75  [,......One hundr]
7F612E9323C0 002C6465 64C10202 6E694E0B 20797465  [ed,....d.Ninety ]
7F612E9323D0 656E696E 0202002C 54030BC1 002C6E65  [nine,......Ten,.]
7F612E9323E0 03C10202 6F775403 0202002C 4F0302C1  [.....Two,......O]
7F612E9323F0 002C656E 04800102 6F72655A FE1B0602  [ne,.....Zero....]
Block header dump:  0x01802c95
 Object id on Block? Y
 seg/obj: 0x16c52  csc: 0x00.20fe1a  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1802c90 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.011.00000732  0x010021d0.00e5.0a  --U-    1  fsc 0x0000.0020fe1b
0x02   0x0007.018.000005be  0x01001153.00f8.19  C---    0  scn 0x0000.0020fe04
bdba: 0x01802c95
data_block_dump,data header at 0x7f612e930464
===============
tsiz: 0x1f98
hsiz: 0x20
pbl: 0x7f612e930464
     76543210
flag=--------
ntab=1
nrow=7
frre=-1
fsbo=0x20
fseo=0x1f31
avsp=0x1f11
tosp=0x1f11
0xe:pti[0]      nrow=7  offs=0
0x12:pri[0]     offs=0x1f8e
0x14:pri[1]     offs=0x1f84
0x16:pri[2]     offs=0x1f7a
0x18:pri[3]     offs=0x1f70
0x1a:pri[4]     offs=0x1f5e
0x1c:pri[5]     offs=0x1f4c
0x1e:pri[6]     offs=0x1f31
block_row_dump:
tab 0, row 0, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  80							0
col  1: [ 4]  5a 65 72 6f						Zero
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02							1
col  1: [ 3]  4f 6e 65							One
tab 0, row 2, @0x1f7a
tl: 10 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 03							2
col  1: [ 3]  54 77 6f							Two
tab 0, row 3, @0x1f70
tl: 10 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 0b							10
col  1: [ 3]  54 65 6e							Ten
tab 0, row 4, @0x1f5e
tl: 18 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 64							99
col  1: [11]  4e 69 6e 65 74 79 20 6e 69 6e 65				Ninety nine
tab 0, row 5, @0x1f4c
tl: 18 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 02							100
col  1: [11]  4f 6e 65 20 68 75 6e 64 72 65 64				One hundred
tab 0, row 6, @0x1f31
tl: 27 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 3]  c2 02 02							101
col  1: [19]  4f 6e 65 20 68 75 6e 64 72 65 64 20 61 6e 64 20 6f 6e 65	One hundred and one
end_of_block_dump
End dump data blocks tsn: 4 file#: 6 minblk 11413 maxblk 11413
      
tab 0, row 7, @0x1f1e
tl: 19 fb: --H-FL-- lb: 0x2  cc: 2					1000
col  0: [ 2]  c2 0b							One thousand
col  1: [12]  4f 6e 65 20 74 68 6f 75 73 61 6e 64
tab 0, row 8, @0x1f0a
tl: 20 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c4 02							1000000
col  1: [13]  4f 6e 65 20 6d 69 6c 6c 69 6f 6e 3a 77			One million:w
      
set lines 200 pages 50
COLUMN dump_id      FORMAT a20;
COLUMN dump_payload FORMAT a84;

SELECT t.*, dump(t.id) dump_id, dump(t.payload) dump_payload
FROM   simple_numbers t;

        ID PAYLOAD              DUMP_ID              DUMP_PAYLOAD                                                                        
---------- -------------------- -------------------- ------------------------------------------------------------------------------------
         0 Zero                 Typ=2 Len=1: 128     Typ=1 Len=4: 90,101,114,111                                                         
         1 One                  Typ=2 Len=2: 193,2   Typ=1 Len=3: 79,110,101                                                             
         2 Two                  Typ=2 Len=2: 193,3   Typ=1 Len=3: 84,119,111                                                             
        10 Ten                  Typ=2 Len=2: 193,11  Typ=1 Len=3: 84,101,110                                                             
        99 Ninety nine          Typ=2 Len=2: 193,100 Typ=1 Len=11: 78,105,110,101,116,121,32,110,105,110,101                             
       100 One hundred          Typ=2 Len=2: 194,2   Typ=1 Len=11: 79,110,101,32,104,117,110,100,114,101,100                             
       101 One hundred and one  Typ=2 Len=3: 194,2,2 Typ=1 Len=19: 79,110,101,32,104,117,110,100,114,101,100,32,97,110,100,32,111,110,101
      1000 One thousand         Typ=2 Len=2: 194,11  Typ=1 Len=12: 79,110,101,32,116,104,111,117,115,97,110,100                          
   1000000 One million:w        Typ=2 Len=2: 196,2   Typ=1 Len=13: 79,110,101,32,109,105,108,108,105,111,110,58,119