Re: : Decoding rowid in a datablock.

From: Kurt Van Meerbeeck <kurtvm_at_pandora.be>
Date: Fri, 10 Apr 2009 10:44:50 +0000
Message-ID: <W5993826358240311239360290_at_nocme1bl6.telenet-ops.be>


This happens when a database is migrated from 7 to 8.0 when absolute and relative filenumbers were introduced. see also http://www.ora600.be/DUDE_PRIMER.pdf chapter 7.1

Snippet starts here ----------------------------------------->

7.1 How do we know this was once an Oracle 7 beauty

In Oracle 6 there were only 5 to 6 bits used for the file number. So only a maximum of 2^5-1 (31) or 2^6-1 (63) datafiles could be used (database wide). In Oracle 7 this changed to 10bits or 2^10-1 (1023) datafiles (database wide). However, because of backward compatibility with Oracle 6 an encoding scheme was introduced splitting up the 10bits for file number into 6 and 4 bits and wrapping them around. It really depends on the platform. On intel windows and IBM AIX for example, I’ve seen an 8/2 split.

SVRMGR> select dump(chartorowid('00000000.0000.0001')) from dual ; DUMP(CHARTOROWID('0000000



Typ=69 Len=6: 1,0,0,0,0,0
1 row selected.

SVRMGR> select dump(chartorowid('00000000.0000.ffff')) from dual ; DUMP(CHARTOROWID('00000000.00



Typ=69 Len=6: 255,192,0,0,0,0
1 row selected.

This means that the first file# is :

00000001 00000000 00000000 00000000 -> file# 1

And the maximum file# is :
11111111 11000000 00000000 00000000 -> file# 1023

So the 10bits encoding scheme is like this : LLLL LLLL HH Where L is the low order bits
And H the high order bits

Now let’s open DUDE on a series of these datafiles :

DUDE> Initialising ...
DUDE> Init : creating filenumber map ...
DUDE> Scanning tablespace SYSTEM : BLOCKSIZE = 2048
DUDE> File : G:\sys1orcl.ora resolves to number : 4
DUDE> File : G:\sys2.ora resolves to number : 40

You’ll notice that sys1orcl.ora which is basically the first file of the database has file# equal to 4. And we know that sys2.ora had file# equal to 10. How’s that possible ? File# 1 = 0000 0001 00 (LLLL LLLL HH) EQUALS 4 in Oracle 8 DBA format File# 10 = 0000 1010 00 (LLLL LLLL HH) EQUALS 40 in Oracle 8 DBA format

It’s clear that using the Oracle 8 DBA format encoding on the Oracle 7 wrapped DBA format, results in different file numbers. Basically, the file number shifted 2 bits to the left (or x2x2). This is of course platform specific, but if the first file of SYSTEM has a file number that is a multiple of 2, you probably have a migrated database. So what happened when Oracle 8.0 came along and introduced 2^10-1 or 1023 datafiles per tablespace ! Well – the DBA format stayed the same. However, the file numbers became relative to the tablespace. So 2 datafiles of the same database could have potentially the same file number, but belong to 2 different tablespaces. What happened to the Oracle 7 (absolute) file numbers when it was migrated to Oracle 8. Surely, the mig utility didn’t update the DBA for all blocks ? Let’s check out an Oracle 7 database :

SVRMGR> desc file$
Column Name Null? Type

------------------------------ -------- ----
FILE# NOT NULL NUMBER
STATUS$ NOT NULL NUMBER
BLOCKS NOT NULL NUMBER
TS# NOT NULL NUMBER
SVRMGR> select file#, ts# from file$ ;
FILE# TS#
---------- ----------
1 0
2 1
3 2
4 3
5 8
6 9
7 10
8 7
9 11
10 0
10 rows selected.

Ok – looks logical – we see that tablespace TS#=0 or SYSTEM has 2 datafiles with file#=1 and file#=10.

Let’s do the same after a migration to 8.0 : SVRMGR> desc file$
Column Name Null? Type

------------------------------ -------- ----
FILE# NOT NULL NUMBER
STATUS$ NOT NULL NUMBER
BLOCKS NOT NULL NUMBER
TS# NUMBER
RELFILE# NUMBER
MAXEXTEND NUMBER
INC NUMBER
CRSCNWRP NUMBER
CRSCNBAS NUMBER
OWNERINSTANCE VARCHAR2(30)
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 VARCHAR2(1000)
SPARE4 DATE SVRMGR> select file#,ts# from file$ ;
FILE# TS#
---------- ----------
1 0
2 1
3 2
4 3
5 8
6 9
7 10
8 7
9 11
10 0
10 rows selected.

So – the file# for the datafiles stayed the same. But we can see an add column in file$ - relfile# :

SVRMGR> select file#,relfile#,ts# from file$ ; FILE# RELFILE# TS#
---------- ---------- ----------
1 4 0
2 8 1
3 12 2
4 16 3
5 20 8
6 24 9
7 28 10
8 32 7
9 36 11
10 40 0
10 rows selected.

Here we can clearly see the 2bit shift to the left – the Oracle 7 absolute filenumber became an Oracle 8 relative filenumber.
So the mig utility did not have to :
- update the DBA in a block
- row addresses in chained and migrated rows

<----------------------------------------- Snippet ends here 

Cheers,
Kurt

>----- Oorspronkelijk bericht -----

>Van
: Mathias Magnusson [mailto:mathias.magnusson_at_gmail.com] >Verzonden
: vrijdag
, april
 10, 2009 10:43 AM
>Aan
: oracle-l_at_freelists.org
>Onderwerp
: Fwd: Decoding rowid in a datablock.

>
>I'm trying to document how rowid is used by Oracle and intend to use it for
>a presentation on rowid and how it is used by Oracle. Anyway, I found one
>thing I feel I should document better, but I cannot find anything about.
>
>Looking at the rowid in a block dump it has a format like 01 00 03 00 00 00.
>I believe I've found evidence that this makes it 010 for file, 00300 for
>block and 0000 for row. Block becomes 768 which is exactly what I knew it
>would be, it is also the only row so 0000 for row is correct. The issue is
>the file number. I know it is 4, but 010 is 16 converted to decimal. I think
>I have found indications that it needs to be divided by 4. That of course
>gets me the correct value.
>
>The question is, is this correct?
>
>Is this documented somewhere? What are the three numbers skipped in the file
>id in the rowid used for (it would seem that the file id part of a rowid in
>a block would never contain the value 1, 2, or 3)?
>
>Mathias
>


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 10 2009 - 05:44:50 CDT

Original text of this message