First of all – This book (Oracle Core – by Jonathan Lewis) is great if you really want to know the Oracle internals. And who doesn’t???
It does take some effort reading, but considering the content it’s very well written. Finally I can throw away my old compendium (from same author – Jonathan Lewis) which I got attending a 5 day seminar about 7-8 years ago). It has really started to fall apart after many years use.
I have never really used block dumps myself, so its definitly time to get started. The following is some notes doing some test related to the appendix material and doing block dumps.
First I created the test table and inserted some data (as specified in chapter 3 page 35 (“Setting the Scene”):
conn lj/lj create table t1 (id number, n1 number); insert into t1 values (1,1); insert into t1 values (2,2); insert into t1 values (3,3); commit;
Then we have some data in a block – somewhere? Then we need to find the block identity:
SQL> conn system SQL> select relative_fno, header_block, blocks, extents 2 > from dba_segments where owner='LJ' and segment_name='T1'; RELATIVE_FNO HEADER_BLOCK BLOCKS EXTENTS ---------------------- ---------------------- ---------------------- ---------------------- 4 162 8 1 SQL> select file_name from dba_data_files where relative_fno=4; FILE_NAME -------------------------------------------------------- /u01/app/oradata/ORCL/users01.dbf SQL> select object_id from dba_objects where owner='LJ' and object_name='T1'; OBJECT_ID ---------------------- 81494
This show how the header block of the segment (of object 81494) is located in file 4 (which happens to be USERS.DBF). The segment consist of 1 extent, which should infer the seven last blocks following the header block of 162. Now I could test dumping all blocks of the table:
alter system dump datafile 4 block 162; alter system dump datafile 4 block 163; alter system dump datafile 4 block 164; alter system dump datafile 4 block 165; alter system dump datafile 4 block 166; alter system dump datafile 4 block 167; alter system dump datafile 4 block 168; alter system dump datafile 4 block 169;
Or in one statement …
alter system dump datafile 4 block min 162 block max 169 ;
When I now return to the trace files and lock at the dump I find the following:
oracle:/u01/app/oracle/diag/rdbms/orcl/ORCL/trace$ ls -ltr ... -rw-r----- 1 oracle oinstall 655 2012-01-14 11:43 ORCL_ora_3384.trc -rw-r----- 1 oracle oinstall 13867847 2012-01-14 12:48 alert_ORCL.log -rw-r----- 1 oracle oinstall 470 2012-01-14 13:39 ORCL_ora_5830.trm -rw-r----- 1 oracle oinstall 194210 2012-01-14 13:39 ORCL_ora_5830.trc oracle:/u01/app/oracle/diag/rdbms/orcl/ORCL/trace$ cat ORCL_ora_5830.trc ... # Some lines are skipped Start dump data blocks tsn: 4 file#:4 minblk 162 maxblk 162 Block dump from cache: Dump of buffer cache at level 4 for tsn=4, rdba=16777378 BH (0x189fda998) file#: 4 rdba: 0x010000a2 (4/162) class: 4 ba: 0x189c12000 set: 22 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25 dbwrid: 0 obj: 81494 objn: 81494 tsn: 4 afn: 4 hint: f hash: [0x1b596db10,0x1b596db10] lru: [0x187f72e60,0x189fdb070] ckptq: [NULL] fileq: [NULL] objq: [0x1af16e5e0,0x1af16e5e0] objaq: [0x1af16e5d0,0x1af16e5d0] st: XCURRENT md: NULL tch: 3 flags: block_written_once redo_since_read LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1] cr pin refcnt: 0 sh pin refcnt: 0 Block dump from disk: buffer tsn: 4 rdba: 0x010000a2 (4/162) scn: 0x0000.014f2816 seq: 0x01 flg: 0x04 tail: 0x28162301 frmt: 0x02 chkval: 0x9493 type: 0x23=PAGETABLE SEGMENT HEADER Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007F339C69A600 to 0x00007F339C69C600 7F339C69A600 0000A223 010000A2 014F2816 04010000 [#........(O.....] 7F339C69A610 00009493 00000000 00000000 00000000 [................] ... # Hex dump of block skipped Start dump data blocks tsn: 4 file#:4 minblk 163 maxblk 163 *** 2012-01-14 13:39:24.010 Block dump from cache: Dump of buffer cache at level 4 for tsn=4, rdba=16777379 BH (0x188f98ea8) file#: 4 rdba: 0x010000a3 (4/163) class: 1 ba: 0x188528000 set: 17 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25 dbwrid: 0 obj: 81494 objn: 81494 tsn: 4 afn: 4 hint: f hash: [0x1b5a0be80,0x1b5a0be80] lru: [0x188f98e60,0x188f990c0] ckptq: [NULL] fileq: [NULL] objq: [0x1b0a9d390,0x1b0a9d390] objaq: [0x1b0a9d380,0x1b0a9d380] st: XCURRENT md: NULL tch: 1 flags: block_written_once redo_since_read LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1] cr pin refcnt: 0 sh pin refcnt: 0 Block dump from disk: buffer tsn: 4 rdba: 0x010000a3 (4/163) scn: 0x0000.014f2816 seq: 0x01 flg: 0x04 tail: 0x28160601 frmt: 0x02 chkval: 0x19ed type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007F339C69A600 to 0x00007F339C69C600 7F339C69A600 0000A206 010000A3 014F2816 04010000 [.........(O.....] 7F339C69A610 000019ED 00000001 00013E56 014F2815 [........V>...(O.] ... # Hex dump of block skipped Start dump data blocks tsn: 4 file#:4 minblk 164 maxblk 164 Block dump from cache: Dump of buffer cache at level 4 for tsn=4, rdba=16777380 BH (0x188fb0f68) file#: 4 rdba: 0x010000a4 (4/164) class: 1 ba: 0x1887b0000 set: 18 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25 dbwrid: 0 obj: 81494 objn: 81494 tsn: 4 afn: 4 hint: f hash: [0x1b59aa1f0,0x1b59aa1f0] lru: [0x183f6eab0,0x185fe0130] ckptq: [NULL] fileq: [NULL] objq: [0x1b0a5a030,0x1b0a5a030] objaq: [0x1b0a5a020,0x1b0a5a020] st: XCURRENT md: NULL tch: 3 flags: block_written_once redo_since_read LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1] cr pin refcnt: 0 sh pin refcnt: 0 Block dump from disk: buffer tsn: 4 rdba: 0x010000a4 (4/164) scn: 0x0000.014f2856 seq: 0x01 flg: 0x06 tail: 0x28560601 frmt: 0x02 chkval: 0xe40d type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007F339C69A600 to 0x00007F339C69C600 7F339C69A600 0000A206 010000A4 014F2856 06010000 [........V(O.....] 7F339C69A610 0000E40D 00000001 00013E56 014F2816 [........V>...(O.] ... # Hex dump of block skipped
The same output is similar for the last 5 blocks. But – which block contains the data? When looking for 3 rows in a 190K file these might be hard to spot.
-rw-r----- 1 oracle oinstall 190K 2012-01-14 13:39 ORCL_ora_5830.trc
If I look in the data dictionary using the DBMS_ROWID package I see that the data is in block 164:
SQL> select 2 > dbms_rowid.rowid_relative_fno(rowid) relative_fno, 3 > dbms_rowid.rowid_block_number(rowid) block_nr 4 > from lj.t1; RELATIVE_FNO BLOCK_NR ------------------------------------ ------------------------------------ 4 164 4 164 4 164
But if we look below the huge “Hex dump” section, we’ll find the “block_row_dump”. In the dump of block 163 this is empty, but in the next block we’ll find our 3 rows:
... block_row_dump: end_of_block_dump End dump data blocks tsn: 4 file#: 4 minblk 163 maxblk 163 Start dump data blocks tsn: 4 file#:4 minblk 164 maxblk 164 ... block_row_dump: tab 0, row 0, @0x1f8f tl: 9 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [ 2] c1 02 tab 0, row 1, @0x1f86 tl: 9 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 03 col 1: [ 2] c1 03 tab 0, row 2, @0x1f7d tl: 9 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 04 col 1: [ 2] c1 04 end_of_block_dump End dump data blocks tsn: 4 file#: 4 minblk 164 maxblk 164
I must admit spending quite some time wondering how to interpret the “c1 02” codes. BUT in accordance to Jonathan Lewis (page 42) this is Oracle’s internal representation of the descimal 1. “c1 03” is representing the decimal 2, “c1 04” is 3, and so on.
This is what Oracle write about the Internal Numeric Format in the Oracle Documentation (this from 10g doc):
Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 10^2, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length. Taking this into account, the column size in bytes for a particular numeric data value NUMBER(p), where p is the precision of a given value, can be calculated using the following formula: ROUND((length(p)+s)/2))+1 where s equals zero if the number is positive, and s equals 1 if the number is negative. Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations. Zero and negative infinity each require 1 byte; positive infinity requires 2 bytes.
I must admitt that looking at these dumps are very interesting, and I’m looking forward to doing som more research. If you don’t have a copy of Jonathan’s book “Oracle Core” yet, you should really considering getting it.
Post a Comment