Re: : Decoding rowid in a datablock.
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-lReceived on Fri Apr 10 2009 - 05:44:50 CDT