Let me start saying, sorry, for the really bad English composition of this doc, it was the first time I wrote a doc like this in English, and although it has being a long time since I did it, and hopefully my English is much better now, after 6 years in England and now in America, I never had time to review and correct it, so please keep focused on the technical aspects of this article and ignore the rest :), one day I will properly rewrite it. (12/30/2013).

HOW TO READ AN ORACLE DATAFILE OUT OF AN INSTANCE 

 

The idea of this document in first place is understand how Oracle store the data, also there is a possibility to create a free tool to recover or extract data, such as in critical situations. 

 

To do this research I’ve used a lot of Oracle documentation and a lot of tests by myself, so perhaps you can find any wrong information here, that was something I’ve supposed, so please let me know, anyway, what I’ve found is how to read any oracle datafile corrupted or not, with an online database or not, using free tools like dd and od, and the most important how to convert the data back to the original format. 

 

Since this is just a “research phase”, we are going to use these tools: 

dd – used to copy a datafile or just a specific piece of it. 

od – used to dump the datafile and convert the binary data in hexadecimal. 

Pl/sql – used to convert the hexadecimal data, back to the original format. 

 

But for sure to create an operational tool we must use C++ or just C, with a C program we can do all the steps at once. 

 

First I’m going to explain the structure of an oracle block, and what is behind the scenes when oracle stores the data. 

 

 

 

The first point about the oracle blocks is the difference between UNIX and Linux/Windows. In any Unix I’ve tested (HP,Sun,AIX), the data inside the block are stored the same way (look the data are stored the same way, but the the header of the block can be different, also for different versions), for Linux and Windows the data and some pieces of the header have each pair of bytes inverted, we are going to check that below. 

 

Another very important consideration are the initial and end of each block, so far I couldn’t map all the types, I still have some work to do on this area, and this is very important to make our life easier when finding the records. 

 

So the first thing I want to demonstrate is the difference between Unix and Linux. 

 

And then what means each piece of the dump. 

 

Below we have a dump from Solaris and other from Linux. 

 

DUMP OF A BLOCK - SOLARIS – USING OD 

 

0602 0000 0200 0003 0003 261c 0000 0102 

0000 0000 0100 0000 0000 6058 0003 261a 

0000 0000 0001 0300 0000 0000 0003 000c 

0000 00a8 00c0 052b 008c 2f00 2003 0000 

0003 261c 0001 0003 ffff 0018 1f77 1f5f 

1f5f 0000 0003 1fa2 1f8c 1f77 0000 0002 

0000 0000 0000 6055 4000 0000 0200 0003 

0000 0004 0200 0007 0000 0005 0000 0000 

0000 0000 0000 0000 0000 0000 0000 0000 

0000 0000 0000 0001 0001 0100 0001 0000 

0000 0000 0000 0000 0200 0007 0200 000b 

0000 0000 0000 0000 0000 0000 0000 0000 

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 

 

DUMP OF A BLOCK – LINUX – USING OD  

 

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 

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 

 

Take a look on the difference of these 2 lines in red… 

 

These are the same data, but on the Linux block each pair is inverted, that is the main difference between Unix and (Linux/Windows).  

 

The reason is that, one is BIG-ENDIAN and the other LITTLE-ENDIAN, here is a link that explains in more details: http://en.wikipedia.org/wiki/Endianness

 

OD on Linux: 

Dba – data block address - 009f 0140 

 

OD on Unix but in a Linux block: 

Dba - data block address - 9f00 4001 

 

AND BOTH CASES THE VALUES IS 0140 009F 

 

 

Saying that there is some information that we don’t need to invert to get the right value, see the below piece of the Linux block: 

 

9dd6 0000 0001 0019 c633 0000 86cf 000d 

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

 

These are the object id and the row directory, they are not inverted. 

 

Well I hope you don’t give up now and continue to read the document, this was just a little detail, heheh. 

 

Now let’s talk about each piece of the oracle block, still using the same block as above, but now let’s use only the Linux block, that is the one we are going to base our tests. 

 

 

DUMP OF AN ORACLE10G BLOCK LINUX – USING OD TOOL 

 

-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 

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 

 

I’m going to explain the most important pieces of the block, I’ve painted with different colours and separated by “-“, will be some pieces I’m not going to explain here, the reason is because I don’t know yet what they are, there are some information we can convert direct to decimal and get the final result others we need to apply some rules, lets take a look: 

 

Also the inverted bytes I’m going to show how it would appear in the UNIX environment.  

 

Data block header – Cache layer 

a206 – 06a2– This is the initial of the block header that also represents the block type and format. 

06 - Block type

a2 - Block Format

009f 0140 – 0140 009f – This is the dba - data block address. 

8712 000d - 000d 8712 - This is the SCN (system change number), here is stored the actual scn of the block. Also, used to setup the end of the block. 

05 – This is the seq. 

06 – This is the 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. 

9dd6 – chkval – not sure what is that for. 

 

Block types I've discovered so far:

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  

Where: 

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. 

 

 

THE SAME DUMP USING – ALTER SYSTEM DUMP DATAFILE 

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 

     76543210 

flag=-------- 

ntab=1 

nrow=3 

frre=-1 

fsbo=0x18 

fseo=0x1f57 

avsp=0x1f3f 

tosp=0x1f3f 

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

0x12:pri[0]     offs=0x1f82 

0x14:pri[1]     offs=0x1f6c 

0x16:pri[2]     offs=0x1f57 

block_row_dump: 

tab 0, row 0, @0x1f82 

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

col  0: [ 2]  c1 02 

col  1: [ 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 

end_of_block_dump 

 

 

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. 

 

 

CHAR / 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): 

                  RODRIGO 

 

 

 

 

NUMBER / FLOAT 

 

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  

 

DATE 

 

The date data type contains: 

Century 

Year 

Month 

Day 

Hour 

Minute 

Second 

 

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 

CREATE TABLESPACE TEST_BLOCK 

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  

values(6,'RIGHETTI',SYSDATE+6); 

 

commit; 

 

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

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

from TEST_BLOCK  

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 

WHERE ADDR IN (SELECT PADDR  

                 FROM V$SESSION 

                     WHERE SID = (SELECT DISTINCT SID  

                                      FROM V$MYSTAT)) 

And p.name = 'user_dump_dest'; 

 

 

-- in my case 

/oracle/admin/ims_test/udump/ims_test_ora_5512.trc 

 

-- 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 

 

VERY IMPORTANT DETAIL 

 

■▪▪▪▪▪▪ 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; 

 

 

declare 

   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 

begin 

     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); 

                              var3:=1; 

                         var4:=1; 

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

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

                    var3:=0; 

                var4:=0; 

                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); 

                    var3:=0; 

                var4:=0; 

                block := block+1;  

         elsif var4 = 1 then 

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

       end if; 

     end loop; 

     commit; 

end; 

 

 

-- 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%'; 

 

ID     CAMPO 

 

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 

 

ID     CAMPO 

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) 

as 

 

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

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

            

begin 

    -- 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, 

                         id 

                         from rectable 

                     where id between inib and fimb) loop  

 

                checkvar:=0; 

 

-- starting the assemble based on the first position 

 

              if i.id = linha1 and tam > 16 then 

                          if posicao = 1 then 

                                  reg:=i.pos1||i.pos2||i.pos3||i.pos4||i.pos5||i.pos6||i.pos7||i.pos8||i.pos9||i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                          if posicao = 2 then 

                                 reg:=i.pos2||i.pos3||i.pos4||i.pos5||i.pos6||i.pos7||i.pos8||i.pos9||i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                          if posicao = 3 then 

                                 reg:=i.pos3||i.pos3||i.pos5||i.pos6||i.pos7||i.pos8||i.pos9||i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 4 then 

                                 reg:=i.pos4||i.pos5||i.pos6||i.pos7||i.pos8||i.pos9||i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 5 then 

                                 reg:=i.pos5||i.pos6||i.pos7||i.pos8||i.pos9||i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 6 then 

                                 reg:=i.pos6||i.pos7||i.pos8||i.pos9||i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 7 then 

                                 reg:=i.pos7||i.pos8||i.pos9||i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 8 then 

                                 reg:=i.pos8||i.pos9||i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 9 then 

                                 reg:=i.pos9||i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 10 then 

                                 reg:=i.pos10||i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 11 then 

                                 reg:=i.pos11||i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 12 then 

                                 reg:=i.pos12||i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 13 then 

                                 reg:=i.pos13||i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 14 then 

                                 reg:=i.pos14||i.pos15||i.pos16; 

                           end if; 

                           if posicao = 15 then 

                                 reg:=i.pos15||i.pos16; 

                           end if; 

                           if  posicao = 0 then 

                                 reg:=i.pos16; 

                           end if; 

                           checkvar:=1; 

              end if; 

 

              if checkvar =0 then 

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

                                  reg:=reg||i.pos1; 

                           end if; 

                                

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

                                  reg:=reg||i.pos2; 

                           end if; 

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

                                  reg:=reg||i.pos3; 

                           end if;                                

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

                                  reg:=reg||i.pos4; 

                           end if; 

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

                                  reg:=reg||i.pos5; 

                           end if; 

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

                                  reg:=reg||i.pos6; 

                           end if; 

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

                                  reg:=reg||i.pos7; 

                           end if; 

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

                                  reg:=reg||i.pos8; 

                           end if; 

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

                                  reg:=reg||i.pos9; 

                           end if; 

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

                                  reg:=reg||i.pos10; 

                           end if; 

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

                                  reg:=reg||i.pos11; 

                           end if; 

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

                                  reg:=reg||i.pos12; 

                           end if; 

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

                                  reg:=reg||i.pos13; 

                           end if; 

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

                                  reg:=reg||i.pos14; 

                           end if; 

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

                                  reg:=reg||i.pos15; 

                           end if; 

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

                                  reg:=reg||i.pos16; 

                          end if; 

                          

                         end if; 

                   

              if length(reg)/2 = tam  then 

                      insert into regrecov values (reg); 

                      commit; 

                      reg:=''; 

        end if; 

 

                   

       end loop; 

 

 

end; 

 

 

-- 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 

 

BEGIN 

       FOR I IN (SELECT N.ID IBLOCK, E.ID EBLOCK  

              FROM  

              (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||');'); 

    else 

      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||');'); 

     else 

    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||');'); 

    else 

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

   end if; 

                                                      

END LOOP;                                                       

       END LOOP; 

end; 

/           

 

 

 

--- 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; 

 

CAMPO 

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

2c010302c103074c4554494349410777c50204161f01 

2c010302c10207524f445249474f0777b2061d0b1f01 

2c010302c10708524947484554544907786b08010c2101 

2c010302c1060442454c4107786b090c0c0101 

2c010302c1050552554249410777b3030c0a1a04 

2c010302c1040652454e4154410778640516170101 

 

■▪▪▪▪▪▪▪ 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 N.ID IBLOCK, E.ID EBLOCK  

              FROM  

              (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) 

 

    IBLOCK     EBLOCK 

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

       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; 

 

    LINHA1   POSICAO1     LINHA2   POSICAO2     LINHA3   POSICAO3 

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

       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: 

 

SHOW_CHAR: 

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

 

CREATE OR REPLACE FUNCTION SHOW_CHAR(VAR1 IN VARCHAR2) 

RETURN VARCHAR2 

AS 

   VAR2 VARCHAR2(100); 

   var3 varchar2(100); 

   cont number; 

BEGIN 

     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')); 

            else 

                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; 

end; 

 

SHOW_DATE: 

This function is going to convert the date 

 

CREATE OR REPLACE FUNCTION SHOW_DATE(VAR1 IN VARCHAR2) 

RETURN VARCHAR2 

AS 

   var3 date; 

   cont number; 

   sec  number; 

   ano  number; 

   mes  number; 

   dia number; 

   hora number; 

   minu  number; 

   seg number; 

BEGIN 

 

  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; 

end; 

/         

 

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 

Email: Rodrigo.righetti@gmail.com