| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Data Dictionary: relative_fno question
Relative FNO doesn't actually have anything to do with partitioning at all.
The story is this: in Oracle 7, you were allowed 1022 datafiles per database. The reason for that limitation? Oracle's ROWID format (which references file, block and row numbers) reserves 10 bytes for the file component. 2^10 is (if memory serves) 1024. Take off a bit for overhead, and you arrive at the 1022 (and it *is* 1022, despite what Oracle press books might say about 1023).
Now, along comes Oracle 8. "Thou shalt have up to 65,536 datafiles per database," said Oracle. Because we realise you need to grow these things big, and 1022 files doesn't cut it. Unfortunatelt 2^10 is still 1024, so if Oracle was going to turn round and now let you have 64 times as many files, they would have had to change the ROWID format to permit 16 bytes for the file component (2^16 being 64K).
But tacking an extra 6 bytes onto the file component wasn't really an option: doing so would have broken every Oracle 7 database out there. Therefore, Oracle got clever: we must keep the 10 bytes for the file number component (meaning that we can only count from 1 to 1024). But when you reach 1024, we'll just re-set the counter to 0 and start from scratch again. That of course will mean that your database could well have more than two files identified with the same number (file number 6, let's say). So we have to have some way of distinguishing *this* file 6 from *that* file 6.... and the mechanism they came up with was the OBJECT# (subtly taking advantage of the fact that if table EMP has been created in DATA, then bits of EMP can't also exist in tablespace USERS. So if I know the object ID, I can work out it's EMP you're talking about; that takes me to the right tablespace; and now I know which file number 6 you're talking about. There's a hidden rule here, though: no tablespace can contain more than 1022 data files, because then it would be possible to have two file 6's in the same tablespace, and at that point the game's up.
What this meant, of course, was the file number was no longer absolute ("When I tell you file 6, there can be no argument about which file I'm talking about) but relative ("When I tell you file 6, I also have to tell you which object (and hence tablespace) we're on about, because there could be another file 6 lurking somewhere else.
Hence the use of the term 'relative file number' (though you can still use the absolute file number up until the point where you add the 1023rd datafile to your database and recycle the file numbers).
As to your specific query regarding apparently missing data files when querying dba_segments, perhaps this test will help explain what is going on:
SQL> create tablespace barney
2 datafile 'd:\oracle\oradata\oemrep\barney1.dbf' size 10m;
Tablespace created.
SQL> alter tablespace barney add
2 datafile 'd:\oracle\oradata\oemrep\barney2.dbf' size 10m;
Tablespace altered.
[So now I have two-datafile tablespace]
SQL> create table wilma
2 (col1 char(5))
3 tablespace barney;
Table created.
[and a segment created somewhere within it].
First things first: what are the data file numbers I've just added to my database:
SQL> select file# from v$datafile where name like '%BARNEY%';
FILE#
14
15
Which file actually got used for the table's initial extent?
SQL> select segment_name, file_id from dba_extents 2 where segment_name ='WILMA';
SEGMENT_NAME
FILE_ID
[So file 14 it is]
Now let's force file 15 to be used to store an extent:
SQL> alter table wilma
2 allocate extent (datafile 'd:\oracle\oradata\oemrep\barney2.dbf');
Table altered.
And let's check that:
SQL> select segment_name, file_id from dba_extents 2 where segment_name ='WILMA';
SEGMENT_NAME
FILE_ID
So table WILMA definitely has extents on both data files.
Now, your original query was:
select distinct relative_fno from dba_segments;
I'll modify that just slightly, so that I only list the files we're really interested in:
SQL> select distinct relative_fno from dba_segments 2 where segment_name='WILMA';
RELATIVE_FNO
14
And as you correctly reported, the query appears to have lost sight of the fact that WILMA definitely has an extent on file 15.
But *that's because this is the DBA_SEGMENTS view*. It reports one row per segment, however many files its been stored on. Take a look at the structure of DBA_SEGMENTS, as an example: it contains columns called "HEADER_FILE" and "HEADER_BLOCK" -meaning the *first* file and the first block of the segment. In other words, it tells you where your table *starts*, not where it ends up. And however many files I add into my tablespace, and however many extents WILMA acquires, the fact that it started on file 14 will never, ever change (until you drop it and re-create it, of course).
To correctly see WILMA on all her data files, you need to see where each *extent* has ended up. And that's the job of DBA_EXTENTS. Hence my earlier query above.
So no, you're not doing anything wrong, and Oracle most certainly can make use of multiple data files within the one tablespace.
I hope that helps.
Regards
HJR
"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
news:b04aog$lmc3o$1_at_ID-152732.news.dfncis.de...
> > > On Wed, 15 Jan 2003 11:47:08 +0100, "Jan Gelbrich" > > <j_gelbrich_at_westfalen-blatt.de> wrote: > > > > >Any hints greatfully appreciated. > > > > > > relative_fno is implemented because of partitioning and sorry to say > > so but this is definitely a doc question (The Oracle Reference manual > > describes all dictionary views) > > > > Thank You, Sybrand. I immediately re-readed the online doc (of 8.1.7), > but I am sorry: I cannot see any link to what you are indicating: > The doc is (of course) very short: > <citation> > DBA_SEGMENTS > ... > RELATIVE_FNO NUMBER Relative file number of the segment header > > DBA_DATA_FILES > ... > RELATIVE_FNO NUMBER Relative file number > </citation> > > Searching the entire doc by the docs phenomenal java searching window > by entering "RELATIVE_FNO" and such (including googling around) did not > bring me much further ... > and that is why I put my question - which maybe silly, but no matter - > *here* ... > > To come back to facts, I do _not_ use *any* partitioned objects. But even > without having > anything to do with partitioning, tablespaces can have more than one data > file - > and some actually do. > > Does RELATIVE_FNO only begin to have a meaning by starting partitioning ? > I find it hard to beleive - sorry ... > > So I still have no clue: why cannot I see all data file (fno) in > DBA_SEGMENTS ? > > > regards, > Jan > > > > >Received on Wed Jan 15 2003 - 18:22:36 CST
 |
 |