Ages ago I had access to several trainings, one of them while studying the inner structure of an Oracle database block using  I decided to write an article about it, I already knew how to manipulate the block using Oracle BBED(Block Browser and Editor) and I had played with Oracle DUL(Data Unloader) from Bernard van Duijnen.

But you know that feeling when you get a new toy and although you are really happy playing with it, you still want to know more about how it works, so you break it apart and try to understand it?

Unfortunately putting it back together tends to be complicated and in my case it meant losing most of my toys, point being, anything that you see here, DON’T TRY IN PRODUCTION !!!

About this article that I wrote long ago, it was also one of my first attempts to write in English, so it was quite poor in that sense; not that you should expect that I have improved a lot, but after a few years using the language I hope this version will be a little bit better.

In essence I’m just trying to simplify and make this version more readable. Back then when I first wrote it, I was using 10g, and I’m not updating the content with the most recent version changes from 11g or 12c, although I should, most of the inner data block structure has not changed.

Back to the topic, as I mentioned on the title, I wanted to be able to read/interpret binary data from any Oracle datafile without using an Oracle instance to open it, and not using other Oracle tools such as BBED or DUL, it had to be a “manual” dismantling to have a real feeling and understanding of what Oracle is really doing with the data.

In no shape or form this article intends to cover all types of blocks and data that can be stored in an Oracle database, this is just to show how one can act like a kid again that breaks apart the new toy to understand how it works, and if you learn just a bit from it, great, but it is not one of those things that will change your DBA life :) so if you want to stop now and go back to drink, it is not a bad idea !

To “dismantle” the datafile, I had to use some tools:

dd - Unix tool that can be used to copy files or part of files from any device/source, look at for refence.

Using DD I will copy/extract the blocks that I’m interested in.

od - This tools can be used to dump the contents of binary files into several readable formats such as ASCII, Hexa, Decimal or Octal, for reference:

With OD I will convert the binary blocks into a readable format using Hexadecimal.

PL/SQL - This being a quick study and also with my C skills very rusty, I decided to create a simple code using PL/SQL, to transform and reload the data back into the database. (I know, I said before I didn’t want to use Oracle tools for this exercise, but PL/SQL doesn’t count in this case).

With PL/SQL I can apply the Oracle rules and convert the Hexadecimal back into the the original format and insert the data back.

Before we start doing all this crazy stuff, let’s cover some concepts and the Oracle database block structure.

Depending on the OS you use, you need to deal with the “Endianness” of the block in order to correct “read” the data, I’m not going to get into details on this as it is a very lenght subject, for the purpose of this paper, it is just important to note the different byte order from traditional Unix systems(Big-Endian) vs Linux/Windows(Little-Endian). As you can observe on the example below the data dumped from Linux has the bytes inverted. To understand the reasons behind this, please read this article

OD dump Snippet from blocks with the same content from Solaris and Linux.


0000 0000 0000 0000 0000 002c 0103 02c1 

0406 5245 4e41 5441 0778 6405 1617 0101 

2c01 0302 c103 074c 4554 4943 4941 0777 

c502 0416 1f01 2c01 0302 c102 0752 4f44 

5249 474f 0777 b206 1d0b 1f01 261c 0601 




010e 0000 010e 0000 0000 0000 02db 0080 

160a 001a 0000 0139 0104 2ce0 0301 c102 

0604 4552 414e 4154 7807 0564 1716 0101 

012c 0203 03c1 4c07 5445 4349 4149 7707 

02c5 1604 011f 012c 0203 02c1 5207 444f 

4952 4f47 7707 06b2 0b1d 011f 0605 8712 


Note the difference of these 2 lines in red… 


They are the same data, but on the Linux block each pair is inverted, that is due to the endianness difference between those platforms.


Usually when reading an article like this, that is the point where I would stop and drink at least 2 beers before I continue, just some tip to help digest this stuff. 


Now let’s see the entire block and talk about each section that is color and “-” separated:


Hexadecimal extract of OD from an ORACLE10G LINUX block


-a206- 0000 -009f 0140- -8712 000d -0000 -0605

9dd6 0000 0001 0019 -c633- 0000 -86cf 000d- 

0000 -1fe8 1f02- -0032- -0099 0140- -0004 0025 

0158 0000 04f5 0080 0079 0020 2003 0000 

8712 000d 0000 0000 0000 0000 0000 0000 

0000 0000 0000 0000 0000 0000 0000 0000 

0000 0000 – 0100- -0003- ffff -0018- -1f57- -1f3f- 

-1f3f - 0000 -0003- -1f82- -1f6c- -1f57- 00dd 0000 

01d3 0080 0053 0001 0077 0000 0000 0000 

* -- OD will use * to remove “empty(0)” space

010e 0000 010e 0000 0000 0000 02db 0080 

160a 001a 0000 0139 0104 2ce0 0301 c102 

0604 4552 414e 4154 7807 0564 1716 0101 

012c 0203 03c1 4c07 5445 4349 4149 7707 

02c5 1604 011f 012c 0203 02c1 5207 444f 

4952 4f47 7707 06b2 0b1d 011f 0605 8712 


Data block header – Cache layer 

a206 – Block header, which contains the block type and format. 

06 - Block type - Most common type, used for Tables, Indexes and Clustered tables.

a2 - Block Format - Oracle Version related

009f 0140 – dba - data block address. 

        Consist of the File_id and the Block Number on the datafile.

8712 000d - SCN BASE and Wrap (system change number), here is stored the actual scn of the block.

05 – sequence

        A sequence number incremented for each change to a block at the same SCN. If the sequence number wraps, a new SCN must be allocated.

06 – flag

0605 – This is the footer indication, oracle use a combination of this information(Blocktype+seq) plus the second part of the SCN(scnbase) 8712 to create the footer of the block 0605 8712 or 8712 0605, this is necessary to verify if the block is consistent otherwise you have a fractured block. 

Compare the same section from “alter system dump database block” command:

buffer tsn: 5 rdba: 0x0140009f (5/159) 

scn: 0x0000.000d8712 seq: 0x05 flg: 0x06 tail: 0x87120605 

frmt: 0x02 chkval: 0x9dd6 type: 0x06=trans data 

Hex dump of block: st=0, typ_found=1 

Note the byte order is changed on the alter system dump database block.


Documented Block types, there are others not listed here such as HCC or OLTP compressed blocks:

01 - Undo segment header

02 - Undo data block

03 - Save undo header

04 - Save undo data block

05 - Data segment header(temp, index, data)

06 - KTB managed data block (ITL) 

07 - Temp table data block without ITL

08 - Sort Key

09 - Sort Run

10 - Segment free list block

11 - Data file header


Data Block header – Transaction Layer 

c633 – Object ID – 50739(dec) – 50739+1 = object_id 

86cf 000d – 000d 86cf – CSC – Clean out SCN, used for read consistency. 

0032 – ITC - interested transaction count indicating the number of ITL slots. 

1fe8 1f02 – Flag / Type – This flag show if the block is on the free list or not. 

0099 0140 – 0140 0099 – FXN – BDBA – Next block on the free list. 

-0004 0025 

0158 0000 04f5 0080 0079 0020 2003 0000 

8712 000d 0000 0000 0000 0000 0000 0000 

0000 0000 0000 0000 0000 0000 0000 0000 

0000 0000 – ITL list - interested transaction list - The first entry is the ITL number (Itl), then a transaction ID (Xid), an undo block address (Uba), flags indicating the transaction state (Flag), a lock count (Lck) showing the number of rows locked by this transaction (within this block), and then an SCN or free space credit (SCN/Fsc).  


The Transaction Layer size can be affected by the parameters initrans and maxtrans, they will define the size of the ITL, also PCTFREE and PCTUSED may influence it size. 


Data Block Header – Data Layer 

0100 – 0001 – Number of tables in the block, if we are using cluster table we can store more than one table in the same block, that is why we have that information here. 

0003 – Number or rows in the block 

0018 – fsbo – Free space begin offset  

1f57 – fseo - Free space end offset (fsbo and fseo bound the free space. – 8023(dec) – 8023 bytes 

1f3f – avsp – Available space for new entries. – 7999(dec) bytes. 

1f3f – tosp – Total Space 

0000 – pti – that marks the offset where the row directory is going to start, in this case 0. 


Row directory – these are the information that will point out where is the beginning of each row inside the block, so: 

1f82 – 8066(dec) – The first row stat at this byte position inside the block. 

1f6c – 8044(dec) – The second row 

1f57 – 8023(dec) – The third row 



Well, now we can find where our records are, since we know where they start, but instead of start with the math thing let’s get direct to the rows. 


We know oracle writes from the bottom to the top, and the empty space is between the last row and the block header. 


Everything in purple are the rows, I’m going to separate each one and then explain how to read it. 


As we already now this is a Linux block, so I will need to invert the pair, such a work


160a 001a 0000 0139 0104 third row - 2ce0 0301 c102 

0604 4552 414e 4154 7807 0564 1716 0101 

second row - 012c 0203 03c1 4c07 5445 4349 4149 7707 

02c5 1604 011f first row - 012c 0203 02c1 5207 444f 

4952 4f47 7707 06b2 0b1d 011f – footer 0605 8712 


1 – row 

012c 0203 02c1 5207 444f 4952 4f47 7707 06b2 0b1d 011f – inverted 

2c01 0302 c102 0752 4f44 5249 474f 0777 b206 1d0b 1f01 – corrected 


2 – row 

012c 0203 03c1 4c07 5445 4349 4149 7707 02c5 1604 011f – Inverted 

2c01 0302 c103 4c 4554 4943 074941 0777 c502 0416 1f01 – Corrected 


3 – row 

2ce0 0301 c102 0604 4552 414e 4154 7807 0564 1716 0101 – inverted 

E02c 0103 02c1 0406 5245 4e41 5441 0778 6405 1617 0101 – Corrected 


The value e0 can be ignored in this case, as you can see the identification of a new row is the value 2c


So the row overhead of the row is: 

2c01 0302  


2c – Row flag – This flag indicate if the row contains all the columns on the row piece, that means it is not migrated or chained. 

01 – The second position is the lock byte, if this row is being updated, then it will show which ITL is being used. 

03 – The Third position shows the number of columns on the row piece. 

02 – The last position shows the size of the first column in this case 2 bytes. 


Now we know the how to get the position, and how the row starts, to get the other records is just a matter of math. Ex: 








We know the first column has 2 bytes so let’s get the first row and check that: 

2c01 0302 c102 0752 4f44 5249 474f 0777 b206 1d0b 1f01 


2c01 0302 – row overhead, the last bytes show the size of the first column 2 bytes 

c102 – now we have the first column. 

07   - that is the next byte after the first column and it show the size of the next column. 

52 4f44 5249 474f  

07 – the same here, the third column has also 7 bytes. 

77 b206 1d0b 1f01 



So our record is: 

Column 1 - c102 

Column 2 - 52 4f44 5249 474f  

Column 3 - 77 b206 1d0b 1f01 


As you can see, to find out all the columns you need to check the next value after each column, it will show the size of the next one. 


So we can deduce that for each row, we have an overhead of 3 bytes * number of columns. 

Only if we are using cluster table it has another byte, but that is another story. 


Also if we have null in the middle of two columns it is going to be represented as FF, and if you are storing more than 250 bytes in a column it is going to use 3 bytes to show the size. 


Another note: if we are using replication and storing row level SCN we will have an extra 6 bytes per row. 


Now just to validate all that was said before I did a logical dump using: 

Alter system dump datafile … 


So please compare everything we said with this dump, this will make it easier to understand. 


After that let’s check how we are going to transform these hexa codes in the original information. 




buffer tsn: 5 rdba: 0x0140009f (5/159) 

scn: 0x0000.000d8712 seq: 0x05 flg: 0x06 tail: 0x87120605 

frmt: 0x02 chkval: 0x9dd6 type: 0x06=trans data 

Hex dump of block: st=0, typ_found=1 


Block header dump:  0x0140009f 

 Object id on Block? Y 

 seg/obj: 0xc633  csc: 0x00.d86cf  itc: 2  flg: E  typ: 1 - DATA 

     brn: 0  bdba: 0x1400099 ver: 0x01 opc: 0 

     inc: 0  exflg: 0 


 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc 

0x01   0x0004.025.00000158  0x008004f5.0079.20  --U-    3  fsc 0x0000.000d8712 

0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000 


data_block_dump,data header at 0xdf4e264 


tsiz: 0x1f98 

hsiz: 0x18 

pbl: 0x0df4e264 

bdba: 0x0140009f 










0xe:pti[0]      nrow=3  offs=0 

0x12:pri[0]     offs=0x1f82 

0x14:pri[1]     offs=0x1f6c 

0x16:pri[2]     offs=0x1f57 


tab 0, row 0, @0x1f82 

tl: 22 fb: --H-FL-- lb: 0x1  cc: 3 

col  1: [ col  0: [ 2]  c1 02 

7]  52 4f 44 52 49 47 4f 

col  2: [ 7]  77 b2 06 1d 0b 1f 01 

tab 0, row 1, @0x1f6c 

tl: 22 fb: --H-FL-- lb: 0x1  cc: 3 

col  0: [ 2]  c1 03 

col  1: [ 7]  4c 45 54 49 43 49 41 

col  2: [ 7]  77 c5 02 04 16 1f 01 

tab 0, row 2, @0x1f57 

tl: 21 fb: --H-FL-- lb: 0x1  cc: 3 

col  0: [ 2]  c1 04 

col  1: [ 6]  52 45 4e 41 54 41 

col  2: [ 7]  78 64 05 16 17 01 01 




Now it is time to convert the hexadecimal into the original data, and for that we need to know some rules, so far I’ve worked with the most used data types, but the plan is to be able to read all of them. 


We are going to convert back NUMBER, DATE and CHAR or VARCHAR2. 




Starting with CHAR and VARCHAR, these are the simple one, what we need to do is just convert from hexa to decimal and apply the character map conversion like ASCII. 

If we get the first row of our block, let’s check the second column, that I know it is varchar2. 


Column 2 - 52 4f 44 52 49 47 4f  


Converting it to decimal we have, with SQL use the function ex: to_number(‘52’,’XX’): 

           82 79 68 82 73 71 79 


Converting it to ASCII, using SQL use the function ex: CHR(82): 








Well, there are 2 ways of doing this I’m going to start with the scientific one. 


Now let’s check both NUMBER and FLOAT data types, they use the same internal structure. 


Let’s use the first column, and also I’m going to demonstrate another example: 


Column 1 - c102 


The first byte represents the sign byte, if it is greater then 128(dec), then it is a positive number, if it is 128(dec) 80(hex), then it is zero, and if it is smaller then 128(dec) then it is a negative number. 

Also in the first byte there is the offset used to calculate the exponent, the offset is always 65. 


So we can say positive number will have the base sign byte: 


128 + 65 = 193(dec) – BASE SIGN POSITIVE NUMBERS 


For negative numbers we need to invert to find the base sign so the math will start with: 


255 - 128 – 65 = 62(dec) – BASE SIGN NEGATIVE 


Well, now we need to find the exponent to discover the number stored, since we oracle use base 100 to store the data each byte can represent 0 to 99. 


C1(hex) = 193(dec) sign byte 

02(hex) = 02(dec)  value to be discovered 


To find the exponent we need to subtract the sign byte from the base sign, in our first example both the base sign and the sign byte are 193, so our exponent is 0: 


193 – 193 = 0 exponent 

Then we apply this formula: 

second byte -1 *100^exponent 


02 – 1 * 100^0 = 1 


So in our first column we have the value 1. 








Let’s see other examples: 

Nothing better in math than doing a lot of exercises

Imagine these numbers: 


C1 09 – c1(hex) 193(dec) – 09(hex) 09(dec) 

193 – 193 = 0 exponent =>  

09 – 1 * 100^0 = 8 

So c1 09 = 8 



C1 0b – c1(hex) 193(dec) – 0b(hex) 11(dec) 

193 – 193 = 0 exponent => 11 – 1 *100^0 = 10  

So c1 0b = 10 


Numbers over 99 

C2 02 – C2(HEX) 194(DEC) – 02(HEX) 02(DEC) 

194 – 193 = 1 exponent => 02 – 1 * 100^1 = 100 (dec)  

So c2 02 = 100 


c2 02 0d – C2(HEX) 194(DEC) – 02(HEX) 02(DEC) – 0D(HEX) 13(DEC) 

194 – 193 = 1 exponent  

=> in this case we have 2 bytes after the sign so we need to calculate each byte decreasing the exponent-1: 


02 – 1 * 100^1 = 100 

13 – 1 * 100^0 = 12 --- in this case we decreased the exponent by 1 

100 + 12 = 112(dec)  

So c2 02 0d = 112 


c2 02 18 0d – c2(hex) 194(dec) – 02(hex) 02(dec) – 

              18(hex) 24(dec)  – 0d(hex) 13(dec) 



194 – 193 = 1 initial exponent 

02 – 1 * 100^1 = 100 

24 – 1 * 100^0 = 23 

13 – 1 * 100^-1 =   .12 

= 100+23 +0.12 = 123.12  

So c2 02 18 0d = 123.12 


c0 0d – c0(hex) 192(dec) – 0d(hex) 13(dec) 


192 – 193 = -1 

13 – 1 * 100^1 = 0.12  

So c0 0d = 0.12 –  

number between 0 and 1 will have the sign byte < 193(dec) c1(hex) 


Negative numbers: BASE SIGN (62) 


For negative numbers instead of number-1 we have 101 – number, also there is a trailing byte 66(hex) 102(dec) appended, it can be ignored in the calc.  


3e 64 66 – 3e(hex) 62(dec) - 64(hex) 100(dec) –  

           66(hex) 102(dec)=trailing byte 


62 – 62 = 0 exponent 

101 - 100 * 100^0 = 1, just add the negative sign 

So 3e 64 66 = -1 

3e 63 66 – 3e(hex) 62(dec) – 63(hex) 99(dec) – 

           66(hex) 102(dec)=trailing byte 


62 – 62 = 0 exponent 


101 – 99 * 100^0 = 2 

So 3e 63 66 = -2 

3d 5c 2 66 – 3d(hex) 61(dec) – 5c(hex) 92(dec) - 02(hex) 02(dec) 

             66(hex) 102(dec)=trailing byte 


62 – 61 = 1 exponent 

101 – 92 * 100^1 = 900 

101 – 02 * 100^0 = 99 

900 + 99 = 999 

So 3d 5c 2 66 = -999 



Well, I think that is enough, you see it is not so complicated, for sure Oracle could store the data in a simple way, but why they would make our life easier  




The date data type contains: 









So it is going to use 7 bytes to store this information, each byte has its own rule that is: 


Century – excess 100 

Year    - excess 100 

Month   - no rule 

Day     - no rule 

Hour    - excess 1 

Minute  - excess 1 

Second  - excess 1 


Let’s see what does that mean: 



Using our first row column 3: 

Column 3 - 77 b2 06 1d 0b 1f 01 


Decimal conversion 119 178 6 29 11 31 1 


Century – excess 100 => 119 - 100 = 19 

Year    - excess 100 => 178 - 100 = 78 

Month   - no rule    =>           = 6 

Day     - no rule    =>           = 29 

Hour    - excess 1   => 11 – 1    = 10 

Minute  - excess 1   => 31 – 1    = 30 

Second  - excess 1   => 1  - 1    = 0 


So our date is: 


29/06/1978 10:30:00 – my birthday, hehe. 


Other example: 


78  6b  8 3 f  13 8 – hexadecimal 

120 107 8 3 15 19 8 - decimal 


Century – excess 100 => 120 - 100 = 20 

Year    - excess 100 => 107 - 100 = 07 

Month   - no rule    =>           = 8 

Day     - no rule    =>           = 3 

Hour    - excess 1   => 15 – 1    = 14 

Minute  - excess 1   => 19 – 1    = 18 

Second  - excess 1   => 8  - 1    = 7 


So our date is:  


03/08/2007 14:18:07 


That is it … 


We still have to cover LOB, timestamp, long, long raw, varray, etc…. 


But I’m going to do that in a next phase, hope with the help of someone that would like to join me on this challenge. 




With all this information it is time to do some work, as I said before we are going to use basically 3 tools, dd, od and pl/sql. 


About the pl/sql code I’ve written it very basic, and the reason is to be easily understandable for anyone, from junior to senior dba, so don’t swear at me .  


To set up a test environment let’s follow these steps: 


Setting up the test environment: 


Don’t forget to adjust the code, you are not using the same directories that me. 


My database is a 10.2 Linux – block size 8192 


-- creating tablespace 


DATAFILE '/oracle/FLXdata/ims_test/test_block01.dbf' size 128k; 


-- creating table – I’ve used a high pctfree to put few rows per -- --- block 

create table test_block 

(id   number, 

 nome varchar2(10), 

 data   date) 

 tablespace TEST_BLOCK 

pctfree 99 pctused 1; 



-- insert some data 

insert into test_block  

values(1,'RODRIGO',to_date('29/06/1978 10:30:00','dd/mm/yyyy hh24:mi:ss')); 

insert into test_block  

values(2,'LETICIA',to_date('04/02/1997 21:30:00','dd/mm/yyyy hh24:mi:ss')); 

insert into test_block  

values(3,'RENATA',to_date('22/05/2000 22:00:00','dd/mm/yyyy hh24:mi:ss')); 

insert into test_block  

values(4,'RUBIA', to_date('12/03/1979 09:25:03','dd/mm/yyyy hh24:mi:ss')); 

insert into test_block  

values(5,'BELA', to_date('12/09/2007 11:00:00','dd/mm/yyyy hh24:mi:ss')); 

insert into test_block  





-- to verify how many rows per block and which block were used 

select dbms_rowid.rowid_block_number(rowid) block, COUNT(1) 


GROUP BY dbms_rowid.rowid_block_number(rowid) ; 


-- in my case 

     BLOCK   COUNT(1) 

---------- ---------- 

        13          3 

        14          3 




-- We are going to use an external table to read the dump file and process  

-- it with pl/sql 

-- creating directory and external table 


create directory data as '/oracle/FLXdata/ims_test/'; 


drop table dump_table; 


create table dump_table( 

campo varchar2(1000)) 

organization external 

(type oracle_loader  

default directory data 

access parameters 

records delimited by newline 

badfile 'dump.bad' 

discardfile 'dump.dis' 

logfile 'dump.log' 


location ('table_dump.txt') 

reject limit unlimited; 


-- checking the datafile information to dump it logically and with od after 

select file_name, file_id, bytes, BYTES/8192  

from dba_data_files  

where tablespace_name='TEST_BLOCK'; 


FILE_NAME                                    FILE_ID      BYTES BYTES/8192 

-------------------------------------------- ------- ---------- ---------- 

/oracle/FLXdata/ims_test/test_block01.dbf          5     131072         16 



-- dumping the datafile logically just for validation 


Alter system dump datafile 5 block min 1 block max 16; 


-- to verify the name and location of your dump file 

SELECT p.value||'/'||instance_name||'_ora_'||SPID||'.trc'  

FROM V$PROCESS, v$instance, v$parameter p 


                 FROM V$SESSION 

                     WHERE SID = (SELECT DISTINCT SID  

                                      FROM V$MYSTAT)) 

And = 'user_dump_dest'; 



-- in my case 



-- take a look in your file and check if you can find the rows inside it 

-- that part is just to check if our dump with od is with the same info  


■▪▪▪▪▪▪ Now we are going to dump the file with the unix tool OD 

■▪▪▪▪▪▪ With the OD tool we can dump any kind of file in many formats 

■▪▪▪▪▪▪ Like hexa, octal, ascii… 



-- the dump must go to our file used by the external table table_dump.txt 

cd /oracle/FLXdata/ims_test/ 

od -x test_block01.dbf > table_dump.txt 


■▪▪▪▪▪▪ With this we have dumped the whole datafile inside this txt file, and now -- you can check your external table it should be able to read all this dump. 

■▪▪▪▪▪▪ On this file we have all the blocks of the datafile, and our pl/sql code is going to get only the block with rows inside. 


-- checking the external table; 

select * from table_dump; 



■▪▪▪▪▪▪ So far we already have what we need to start restoring the information from this dump, but for study purposes lets use dd and od to dump only the blocks with our data. 


-- creating directory just separate this dump 


mkdir /oracle/FLXdata/ims_test/dump_blocks 


cd /oracle/FLXdata/ims_test/dump_blocks 


-- run this script to generate the commands for you 

-- it is using the package dbms_rowid to find the blocks where we have data 

-- don’t forget to adjust it with the file_name and block size 

-- as said before DD is a unix tool able to copy blocks from any kind of -- -- file 


Set lines 110 

select distinct 'dd if=/oracle/FLXdata/ims_test/test_block01.dbf   ibs=8192 skip='||dbms_rowid.rowid_block_number(rowid)||' count=1 of=test_block_'||dbms_rowid.rowid_block_number(rowid) 

from test_block 

union all 

select distinct 'od -x test_block_'|| dbms_rowid.rowid_block_number(rowid) ||' > test_block_'|| dbms_rowid.rowid_block_number(rowid)||'.txt' 

from test_block; 


-- in my case 


Cd /oracle/FLXdata/ims_test/dump_blocks 

dd if=/oracle/FLXdata/ims_test/test_block01.dbf ibs=8192 skip=13 count=1 of=test_block_13 

dd if=/oracle/FLXdata/ims_test/test_block01.dbf ibs=8192 skip=14 count=1 of=test_block_14 

od -x test_block_13 > test_block_13.txt 

od -x test_block_14 > test_block_14.txt 




■▪▪▪▪▪▪ So in my case I know I’m using blocks 13 and 14 to store all the data 

■▪▪▪▪▪▪ Now open the txt file, and look for the header and footer of the blocks 

■▪▪▪▪▪▪ Also verify all the pieces described before  

■▪▪▪▪▪▪ You will see a lot of information inside the block as well some *, the od tool some time use * to suppress empty lines, to avoid that you can use od –x –v. 

■▪▪▪▪▪▪ Remember I’ve used pctfree 99, because of that Oracle put a lot of unused information inside the block. 

■▪▪▪▪▪▪ If you create a table and use the normal pctfree and pctused you will see a lot of empty space 0000, you can validate that using od –x -v 



■▪▪▪▪▪▪ Now we need to check the header and the footer of each block 

■▪▪▪▪▪▪ I could be done that automatically already since we know how is the head and how the footer is created, (don’t remember? Go to the database header – cache layer), but I decided to use a manual way this time to let this clear. 

■▪▪▪▪▪▪ So use this last dump we did using just the used blocks and check the header and footer. 

■▪▪▪▪▪▪ In my case they are: 


Header - 60c2 

Footer - 0601 c795 


Continuing with the code… 


■▪▪▪▪▪▪ Now we are going to run the below script to separate from the dump_table(external table reading the dump file), the used blocks, since we know the header and the footer we need to adjust the script, again it can automatic, it is just manual to force you to go through all the process. 


■▪▪▪▪▪▪ Getting the right blocks 





-- pl/sql code to separate the blocks empty block from the used blocks 

-- you need to adjust manually the variables with the header and footer 

-- in this example you should have only one footer, but in a real case 

-- the footer can vary if you have different scn for each block during the  

-- dump process 

-- in my case I just have one scn so just one footer 

-- Also we need to find the end of the empty blocks, they will have a  

-- different SCN, so the footer is different, since we are doing this   

-- manually go to your dump file table_dump.txt and look for the empty  

-- block, tip look for the footer of the used blocks and then check the next -- scn position on the next block, if it is different, then you got it. 

-- When we make this automatic will be much easier, but as I said, the only -- way to full understand this is going manually on the steps. 


-- What this code does is pretty simple, no doubt a better and advanced code -- could be written, but that was not the idea. 

-- it is going to read each line compare the position of the header and the -- footer and insert them in the rectable, with the mark of used block or  

-- empty block 


-- Don’t forget to truncate the table and recreate the sequence to run the 

-- process again 


-- creating a table to save the good blocks 

create table rectable (id number,campo varchar2(200)); 


-- creating a sequence used in other process 

drop sequence rectabseq; 


create sequence rectabseq  

start with 1 

increment by 1; 



-- truncating the table from old tests 

truncate table rectable; 




   head varchar2(10) := 'a206'; --header 

   foot1 varchar2(10) := '0601 c795'; --footer 1 

   foot2 varchar2(10) := '0601 c795'; --footer 2 

   foot3 varchar2(10) := '0602 c794'; -- footer empty block 

   var3 number := 0; -- used to control the lines 

   var4 number := 0; -- used to control the lines 

   block number := 1; -- used to control the block number 


     for i in (select campo from dump_table) loop 

         if head = substr(i.campo,9,4) and var3=0 then 

             insert into rectable values (rectabseq.nextval,i.campo||' NBLOCK - '||block); 



        elsif (foot3 = substr(i.campo,39,9)) and var3=1 then 

     insert into rectable values (rectabseq.nextval,i.campo||' EmpBLOCK - '||block); 



                block := block+1;  

elsif (foot1 = substr(i.campo,39,9) or foot2 = substr(i.campo,39,9)) and var3=1 then 

    insert into rectable values (rectabseq.nextval,i.campo||' EBLOCK - '||block); 



                block := block+1;  

         elsif var4 = 1 then 

              insert into rectable values (rectabseq.nextval,i.campo);                  

       end if; 

     end loop; 





-- After that you should be able to see in the rectable the empty blocks and the 

-- used blocks like this: 


-- Empty Blocks 


select * from rectable where campo like '%c794%'; 




1      0300000 a206 0000 000c 0140 c794 000e 0000 0402 NBLOCK - 1 

511    0317760 c005 1602 c102 c005 1602 8001 0602 c794 EmpBLOCK - 1 

1316   0360000 a206 0000 000f 0140 c794 000e 0000 0402 NBLOCK - 4 

1667   0377760 4000 6007 0000 c102 0213 06c1 0602 c794 EmpBLOCK - 4 

1668   0400000 a206 0000 0010 0140 c794 000e 0000 0402 NBLOCK - 5 

2178   0417760 8001 8001 8001 8001 8001 8001 0602 c794 EmpBLOCK – 5 


-- Used Blocks 



select * from rectable where campo like '%c795%'; 


512    0320000 a206 0000 000d 0140 c795 000e 0000 0601 NBLOCK - 2 

803    0337760 4952 4f47 7707 06b2 0b1d 011f 0601 c795 EBLOCK - 2 

804    0340000 a206 0000 000e 0140 c795 000e 0000 0601 NBLOCK - 3 

1315   0357760 4255 4149 7707 03b3 0a0c 041a 0601 c795 EBLOCK – 3 


-- Now that we have the blocks separated we need to put our records piece together 

-- The script below is going to do this, I’ve put here 2 versions, one for Linux and 

-- other for Unix, remember at Linux the pair of bytes are inverted. 


Linux Code: 


--  create table where the assembled records will be 


create table regrecov 

(campo varchar2(100)); 


-- Below er have the procedure that will assemble the records back, as all other  

-- code here, it was created very simple, to be easier to understand the idea and  

-- the process to call that procedure we need to get the line, the byte position and  

-- the size of the row, that is done in other pl/sql process, but as you will see  

-- the process to put all the row pieces together is done by this procedure 


■▪▪▪▪▪▪▪ To understand how the procedure work I need to explain the logic behind it 

■▪▪▪▪▪▪▪ A Block of 8192 bytes dumped with od, will be divided in 512 lines of 16 bytes per line, so when we call this procedure we pass the position of the beginning of the row, this is discovered in other pl/sql code, knowing the beginning of the row, this procedure will assemble all the bytes in just one row and insert it on the regrecov table, if you go through the procedure is very simple to understand how it is doing the assemble, all was done based on a “if” test, again this could be done in a lot of better ways, but here is just to understand the process.  

■▪▪▪▪▪▪▪ The Unix code will be place on the end of the document, since we are not going to use it now. 



create or replace procedure montareg(linha1 number,  

                                     posicao in number, 

                                         tam in number,  

                                     inib in number,  

                                     fimb in number) 



       reg    varchar2(200) :=''; -- variable used to assemble the row 

       checkvar number:=0;  -- flag to control the process 



    -- this for is responsible to read the rectable and separate all the bytes in  

    -- different columns 

    -- if you check the substr function, you will see how I’m correcting the  

    -- inverted bytes here   

    for i in (select substr(substr(campo,9,40),3,2) pos1, 

                            substr(substr(campo,9,40),1,2) pos2,  

                             substr(substr(campo,9,40),8,2) pos3, 

                         substr(substr(campo,9,40),6,2) pos4, 

                             substr(substr(campo,9,40),13,2) pos5, 

                         substr(substr(campo,9,40),11,2) pos6, 

                         substr(substr(campo,9,40),18,2) pos7, 

                         substr(substr(campo,9,40),16,2) pos8, 

                             substr(substr(campo,9,40),23,2) pos9, 

                         substr(substr(campo,9,40),21,2) pos10, 

                             substr(substr(campo,9,40),28,2) pos11, 

                         substr(substr(campo,9,40),26,2) pos12, 

                             substr(substr(campo,9,40),33,2) pos13, 

                          substr(substr(campo,9,40),31,2) pos14, 

                         substr(substr(campo,9,40),38,2) pos15, 

                         substr(substr(campo,9,40),36,2) pos16, 


                         from rectable 

                     where id between inib and fimb) loop  




-- starting the assemble based on the first position 


              if = linha1 and tam > 16 then 

                          if posicao = 1 then 


                           end if; 

                          if posicao = 2 then 


                           end if; 

                          if posicao = 3 then 


                           end if; 

                           if posicao = 4 then 


                           end if; 

                           if posicao = 5 then 


                           end if; 

                           if posicao = 6 then 


                           end if; 

                           if posicao = 7 then 


                           end if; 

                           if posicao = 8 then 


                           end if; 

                           if posicao = 9 then 


                           end if; 

                           if posicao = 10 then 


                           end if; 

                           if posicao = 11 then 


                           end if; 

                           if posicao = 12 then 


                           end if; 

                           if posicao = 13 then 


                           end if; 

                           if posicao = 14 then 


                           end if; 

                           if posicao = 15 then 


                           end if; 

                           if  posicao = 0 then 


                           end if; 


              end if; 


              if checkvar =0 then 

                           if i.pos1 != '2c' and length(reg)/2 < tam then 


                           end if; 


                           if i.pos2 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos3 != '2c' and length(reg)/2 < tam then 


                           end if;                                

                           if i.pos4 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos5 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos6 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos7 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos8 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos9 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos10 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos11 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos12 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos13 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos14 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos15 != '2c' and length(reg)/2 < tam then 


                           end if; 

                           if i.pos16 != '2c' and length(reg)/2 < tam then 


                          end if; 


                         end if; 


              if length(reg)/2 = tam  then 

                      insert into regrecov values (reg); 



        end if; 



       end loop; 






-- Now we are going to run a pl/sql block that will find for us the position of the 

-- the rows inside the block, this code was done for just 3 rows per block,  

-- another code with a loop need to be done to check blocks with more than 3 rows, 

-- but that is for the next document. 


■▪▪▪▪▪▪▪ We could run this block and call the procedure direct, but I rather use the  

■▪▪▪▪▪▪▪ Dbms_output and execute it manually in this study case 


■▪▪▪▪▪▪▪ To understand row it find the position it is simple: 


■▪▪▪▪▪▪▪ This code is going to find 5 things, Line, Position, Size, Initial Block, End Block, and that is what our montareg procedure needs. 


■▪▪▪▪▪▪▪ We know the block fits is 512 lines of the od dump, but if we don’t use the od –x –v option it will ignore some empty space, that dosen’t matter for us because we just need to subtract the number of lines 


■▪▪▪▪▪▪▪ The loop is the key to find all the positions, after the code I’m going to execute each piece of it and explain them, but you can run the code now and get the execute script for the montarec procedure. 


■▪▪▪▪▪▪▪ I know the code it is a little bit ugly, but I couldn’t find an easier way to demonstrate. 




set serveroutput on 





              (select id,campo from rectable where campo lIKE '%NBLOCK%') n, 

              (select id,campo from rectable where campo lIKE '%EBLOCK%') E 

              WHERE SUBSTR(N.CAMPO,50,10) = SUBSTR(E.CAMPO,50,10)) LOOP 

              FOR J IN ( select linha3,  posicao3,  

        ((((linha2-linha3)+1) * 16)-(posicao3+1))-(16-posicao2-1) tam3, 

        linha2,  posicao2, ((((linha1-linha2)+1) * 16) - (posicao2+1))-(16-posicao1-1) tam2, 

        linha1,  posicao1, ((((I.EBLOCK-linha1)+1) * 16) - (posicao1+1))-(16-13-1) tam1                    from(select I.EBLOCK-(512-linha1) linha1, posicao1, 

                              I.EBLOCK-(512-linha2) linha2, posicao2, 

                             I.EBLOCK-(512-linha3) linha3, posicao3 

                   from(select trunc((to_number(substr(linha,16,4),'XXXX')+118)/16) linha1,  

Mod((to_number(substr(linha,16,4),'XXXX')+118),16)-1 posicao1 

,trunc((to_number(substr(linha,21,4),'XXXX')+118)/16) linha2,  

Mod((to_number(substr(linha,21,4),'XXXX')+118),16)-1 posicao2, 

trunc((to_number(substr(linha,26,4),'XXXX')+118)/16) linha3, Mod((to_number(substr(linha,26,4),'XXXX')+118),16)-1 posicao3 

       from (select rownum numrow, SUBSTR(CAMPO,9,39) linha ,CAMPO from rectable 

                 where id between I.IBLOCK  and I.EBLOCK 

                 order by id)  

        where numrow=8))) LOOP 


-- these if are used to correct and mount the output of the montareg prod              


    if j.posicao3 = 0 then 

      dbms_output.put_line('exec montareg('||to_char(j.linha3-1)||','||j.posicao3||','||j.tam3||','||i.iblock||','||i.eblock||');'); 

    elsif j.posicao3 = -1 THEN 

      dbms_output.put_line('exec montareg('||to_char(j.linha3-1)||',15,'||j.tam3||','||i.iblock||','||i.eblock||');'); 


      dbms_output.put_line('exec montareg('||j.linha3||','||j.posicao3||','||j.tam3||','||i.iblock||','||i.eblock||');'); 

    end if; 


   if j.posicao2 = 0 then 

      dbms_output.put_line('exec montareg('||to_char(j.linha2-1)||','||j.posicao2||','||j.tam2||','||i.iblock||','||i.eblock||');'); 

     elsif j.posicao2 = -1 then 

      dbms_output.put_line('exec montareg('||to_char(j.linha2-1)||',15,'||j.tam2||','||i.iblock||','||i.eblock||');'); 


    dbms_output.put_line('exec montareg('||j.linha2||','||j.posicao2||','||j.tam2||','||i.iblock||','||i.eblock||');'); 

   end if; 


   if j.posicao1 = 0 then 

      dbms_output.put_line('exec montareg('||to_char(j.linha1-1)||','||j.posicao1||','||j.tam1||','||i.iblock||','||i.eblock||');'); 

     elsif j.posicao1 = -1 then 

      dbms_output.put_line('exec montareg('||to_char(j.linha1-1)||',15,'||j.tam1||','||i.iblock||','||i.eblock||');'); 


     dbms_output.put_line('exec montareg('||j.linha1||','||j.posicao1||','||j.tam1||','||i.iblock||','||i.eblock||');'); 

   end if; 


END LOOP;                                                       

       END LOOP; 






--- Running the code I got this return, 

--- if the any negative number appear here something went bad, go back and check the  

--- adjustments you did 


exec montareg(799,12,21,512,803); 

exec montareg(801,1,22,512,803); 

exec montareg(802,7,22,512,803); 

exec montareg(1311,15,23,804,1315); 

exec montareg(1313,6,19,804,1315); 

exec montareg(1314,9,20,804,1315); 


-- execute the procedure: 

Truncate table regrecov; 


-- select the table regrecov 


Select * from regrecov; 











■▪▪▪▪▪▪▪ Now you can see all the six records assembled and with the bytes in the right  

order, but before we transform this data back to the original format let’s go through the LOOP used in the pl/sql block used to set up the exec montareg. 




First piece 




              (select id,campo from rectable where campo lIKE '%NBLOCK%') n, 

              (select id,campo from rectable where campo lIKE '%EBLOCK%') E 

         WHERE SUBSTR(N.CAMPO,50,10) = SUBSTR(E.CAMPO,50,10) 



---------- ---------- 

       512        803 

       804       1315 


This first select of the outer loop, is used to get the initial and end of each used block, that was separated earlier in other pl/sql we have executed. So this loop is going to be based only inside these 2 blocks. 



Now we need to start from the bottom to the top of this query to understand 


select linha3,  posicao3,   

        ((((linha2-linha3)+1) * 16)-(posicao3+1))-(16-posicao2-1) tam3, 

        linha2,  posicao2, ((((linha1-linha2)+1) * 16) - (posicao2+1))-(16-posicao1-1) tam2, 

        linha1,  posicao1, ((((I.EBLOCK-linha1)+1) * 16) - (posicao1+1))-(16-13-1) tam1                    from(select I.EBLOCK-(512-linha1) linha1, posicao1, 

                              I.EBLOCK-(512-linha2) linha2, posicao2, 

                             I.EBLOCK-(512-linha3) linha3, posicao3 

                   from(select trunc((to_number(substr(linha,16,4),'XXXX')+118)/16) linha1,  

Mod((to_number(substr(linha,16,4),'XXXX')+118),16)-1 posicao1 

,trunc((to_number(substr(linha,21,4),'XXXX')+118)/16) linha2,  

Mod((to_number(substr(linha,21,4),'XXXX')+118),16)-1 posicao2, 

trunc((to_number(substr(linha,26,4),'XXXX')+118)/16) linha3, Mod((to_number(substr(linha,26,4),'XXXX')+118),16)-1 posicao3 


       from (select rownum numrow, SUBSTR(CAMPO,9,39) linha ,CAMPO from rectable 

                 where id between I.IBLOCK  and I.EBLOCK 

                 order by id)  

        where numrow=8))) 



This query is going to get all the rows between the IBLOCK id and EBLOCK id passed on the first loop, so we can check all the other information. 



select rownum numrow, SUBSTR(CAMPO,9,39) linha ,CAMPO  

     from rectable 

 where id between 512  and 803 – I’ve used the from the first query 

order by id; 


Returned 292 rows not 512, because I’ve used od –x and not od –x –v. 


Now we are going to convert the row directories, you will see I’ve used rownum=8, that is because the first row directory is on this line, as we checked above, for sure, this is just for 3 rows and all other 2 row directories are there, that is why it is only checking 3 rows. But in a C code or even in a pl/sql code we can do it to check as many row directories we have. 




select trunc((to_number(substr(linha,16,4),'XXXX')+118)/16) linha1,  

Mod((to_number(substr(linha,16,4),'XXXX')+118),16)-1 posicao1 

,trunc((to_number(substr(linha,21,4),'XXXX')+118)/16) linha2,  

Mod((to_number(substr(linha,21,4),'XXXX')+118),16)-1 posicao2, 

trunc((to_number(substr(linha,26,4),'XXXX')+118)/16) linha3, Mod((to_number(substr(linha,26,4),'XXXX')+118),16)-1 posicao3 

       from (select rownum numrow, SUBSTR(CAMPO,9,39) linha ,CAMPO from rectable 

                 where id between 512   and 803 -- change here the IBLOCK and EBLOCK 

                 order by id)  

        where numrow=8; 



---------- ---------- ---------- ---------- ---------- ---------- 

       511          7        510          1        508         12 


Sorry I forgot to translate the column name, on the next document I will correct everything I promise, but for reference: 


LINHA = Line in English 

POSICAO = Position in English 


As you can see on the query, it is converting the row directory to decimal, adding 118 bytes (Block header) dividing by 16 (number of bytes in each line), that way we know the line, to get the first byte position it is getting the remain with MOD function, minus 1, then we find the start point of the row 2c. I know that is a lot of information but after you run some tests you will see that is pretty simple. 


The idea here was to demonstrate how we can get the begging of the row using the row directory information. 


And if you look the last step it is getting the size, which is very simple, we just need to subtract one row directory by the other. 


Now let’s get back to our regrecov table, where our records are already assembled and ready to be translated to the original data. 


To help on that I’ve created 2 functions: 



This function will be called by a query and will return the char piece converted 





   VAR2 VARCHAR2(100); 

   var3 varchar2(100); 

   cont number; 


     cont := length(var1)/2; 

       FOR I IN 1..cont LOOP 

        if i =1 then 

              VAR2 := var2||SUBSTR(VAR1,i,2); 

              VAR3 := var3||CHR(to_number(SUBSTR(VAR1,i,2),'XX')); 


                VAR3 := var3||CHR(to_number(SUBSTR(VAR1,length(var2)+1,2),'XX')); 

                VAR2 := var2||SUBSTR(VAR1,length(var2)+1,2); 

       end if; 


       END LOOP; 

       return var3; 




This function is going to convert the date 





   var3 date; 

   cont number; 

   sec  number; 

   ano  number; 

   mes  number; 

   dia number; 

   hora number; 

   minu  number; 

   seg number; 



  sec :=  TO_NUMBER(substr(var1,1,2),'XX')-100+1; 

  ano :=  to_number(substr(var1,3,2),'XX')-100; 

  mes :=  to_number(substr(var1,5,2),'XX'); 

  dia :=  to_number(substr(var1,7,2),'XX'); 

  hora:=  to_number(substr(var1,9,2),'XX')-1; 

   minu :=  to_number(substr(var1,11,2),'XX')-1; 

  seg :=  to_number(substr(var1,13,2),'XX')-1; 



    var3   := to_date(mes||'/'||dia||'/'||ano||' '||hora||':'||minu||':'||seg,'mm/dd/rrrr hh24:mi:ss'); 

       return var3; 




I didn’t create yet a function to convert the number, sorry about that, but since our example is based on numbers between 1 to 6, I just used another approach to return the data. 


So the query we are going to use is below: 


-- if you want for format the date  

alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss'; 


-- to return the number, I just got the second byte and decreased 1, you will see  

-- the result is the same of the formula we used above, because the exponent here  

-- is 0 


-- To separate each column I’ve used substr function, and based on what I know  

-- about the rows like where the date field start 077% 


select decode(substr(campo,9,2),'c1',to_number(substr(campo,11,2),'XX')-1,'') ID, 

       SHOW_CHAR(substr(campo,15,instr(campo,'077')  -15)) name, 

            Show_date(substr(campo,instr(campo,'077')+2,16)) birthdate 

from regrecov 

order by 1; 


    ID NAME                                               BIRTHDATE 

------ -------------------------------------------------- ------------------------- 

     1 RODRIGO                                            29/06/1978 10:30:00 

     2 LETICIA                                            04/02/1997 21:30:00 

     3 RENATA                                             22/05/2000 22:00:00 

     4 RUBIA                                              12/03/1979 09:25:03 

         5 BELA                                               12/09/2007 11:00:00 

     6 RIGHETTI                                           01/08/2007 11:32:00 



So here it is proved how we can bring data from any datafile, for sure, there are a lot of work to do, but this is just a start. 


All this manual process need to be automatic and be very fast, here we have found the basic information necessary to start planning in something real, but we already know that it is feasible. 


If you have any question or it is interested and help me on this idea I will be glad to help and be helped. 



Author: Rodrigo Righetti