Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Data Dictionary: relative_fno question - Thank You VERY much !!!
Dear Howard,
this was definitely the most clarifying response I ever received from this
NG !
Including the afterquestions in the follow ups by Sean and Richard,
You all were making things most understandable.
I realized once more that I still have much to learn ...
Thank You !!!
Jan =)
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> schrieb im Newsbeitrag
news:ANmV9.25033$jM5.66076_at_newsfeeds.bigpond.com...
> 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
> --------------------------------------------------------------------------
--Received on Thu Jan 16 2003 - 12:10:39 CST
> ----- ----------
> WILMA
> 14
>
> [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
> --------------------------------------------------------------------------
--
> ----- ----------
> WILMA
> 14
> WILMA
> 15
>
> 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
> >
> >
> >
> >
> >
>
>