Data Dictionary: relative_fno question
Jan Gelbrich
When I want to see all used data files for the database, of cource this is
easy:
SQL> select distinct relative_fno, substr(file_name,1,30) from
dba_data_files;
RELATIVE_FNO SUBSTR(FILE_NAME,1,30)
------------ ------------------------------
1 /u01/oradata/ora8/system01.dbf
2 /u01/oradata/ora8/rbs01.dbf
3 /u01/oradata/ora8/temp01.dbf
4 /u01/oradata/ora8/tools01.dbf
5 /u01/oradata/ora8/users01.dbf
6 /u02/oradata/ora8/tt_dat01.dbf
7 /u02/oradata/ora8/tt_dat02.dbf
8 /u03/oradata/ora8/tt_temp01.db
9 /u03/oradata/ora8/tt_ind01.dbf
10 /u01/oradata/ora8/rbs02.dbf
11 /u02/oradata/ora8/es_dat02.dbf
12 /u01/oradata/ora8/system02.dbf
13 /u03/oradata/ora8/es_ind01.dbf
14 /u02/oradata/ora8/es_dat01.dbf
15 /u04/oradata/ora8/op01.dbf
16 /u04/oradata/ora8/op_ind01.dbf
17 /u03/oradata/ora8/versand_ind0
18 /sicher/altdaten01.dbf
19 /u03/oradata/ora8/tt_ind02.dbf
20 /sicher/altdaten.dbf
21 /u03/oradata/ora8/versand_ind0
22 /u01/oradata/ora8/versand1.dbf
23 /u04/oradata/ora8/op_ind02.dbf
26 /u01/oradata/ora8/versand.dbf
24 Zeilen ausgewählt.
Once I wanted to find out what segments are in the second data file of
tablespace VERSAND.
It has 2 files:
/u01/oradata/ora8/versand1.dbf [RELATIVE_FNO: 22]
/u01/oradata/ora8/versand.dbf [RELATIVE_FNO: 26]
BUT: in tablespaces with more than one data file, all segments would reside
in
at least one of them, so if I would do the following statement, I would
expect
the same RELATIVE_FNO as in dba_data_files - but as everybody can see,
some are missing !
SQL> select distinct relative_fno from dba_segments;
RELATIVE_FNO
------------
1
2
4
6
7
9
11
12
13
14
15
16
18
19
20
21
26
17 Zeilen ausgewählt.
SQL>
Data File #22 is /u01/oradata/ora8/versand1.dbf, one of two files
for tablespace VERSAND, and this file is still containing
data segments (it is used by 30%), so there must be some segments in it;
and I am not filtering any users out ...
So: why cant I see it in dba_segments ?
Where has file #22 gone ?
Hmmmmmmmmmmmmmmmmmm ...
Shall I use only *one* data file per tablespace ? This is not how I
understood scalability ...
Any hints greatfully appreciated.
Jan
Jan Gelbrich
dictionary views.
Jan Gelbrich
sorry =)
Sybrand Bakker
<j_gel...@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)
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Jan Gelbrich
> On Wed, 15 Jan 2003 11:47:08 +0100, "Jan Gelbrich"
> <j_gel...@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
Xuequn Xu
dba_segments view indicates the file number of the datafile that the
segment header resides, and as you know one segment can span more than one
data file. Those "missing" FNO in the view simply means those datafiles
have no segment header - they are occupied by the "body" of data segments
whose header is elsewhere (in other FNOs). If you really want to get a full
list of FNOs (for datafiles that has data in them), you can check the
dba_extents view - go one step further in a more detail level. A
select distinct relative_fno from dba_extents will get you a full list
of FNOs as you see from dba_data_files (as long as every datafile has data,
i.e. extents, in it).
Jan Gelbrich (j_gel...@westfalen-blatt.de) wrote:
: Hello, this time I have a question about informations from data dtictionary.
Howard J. Rogers
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
----------------------------------------------------------------------------
----- ----------
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_gel...@westfalen-blatt.de> wrote in message
news:b04aog$lmc3o$1...@ID-152732.news.dfncis.de...
Sean M
>
> 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).
Hi Howard-
You've confused me a bit here - when you say "file number," are you
using that figuratively or are you talking about the file# column of
v$datafile (or equivalently the file_id column of dba_data_files)?
v$datafile.file# (or dba_data_files.file_id) is most definitely absolute
and unique - a primary key if you will. v$datafile.rfile# (or
dba_data_files.relative_fno) is the column that can contain duplicates.
Seems like you were saying it's the other way around? Or maybe I just
misunderstood.
Just so you know I'm not crazy:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select count(1)
2 from v$datafile;
COUNT(1)
----------
1284
SQL> select count(distinct file#)
2 from v$datafile;
COUNT(DISTINCTFILE#)
--------------------
1284
SQL> select count(distinct rfile#)
2 from v$datafile;
COUNT(DISTINCTRFILE#)
---------------------
956
Regards,
Sean
Howard J. Rogers
"Sean M" <smck...@adelphia.net> wrote in message
news:3E260C0E...@adelphia.net...
> "Howard J. Rogers" wrote:
> >
> > 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).
>
> Hi Howard-
>
> You've confused me a bit here - when you say "file number," are you
> using that figuratively or are you talking about the file# column of
> v$datafile (or equivalently the file_id column of dba_data_files)?
Pedantically, and technically, I'm talking about the file number component
of the ROWID, which will match with the relative file number visible in
whatever view you care to look at. A worthwhile clarification, so thanks.
> v$datafile.file# (or dba_data_files.file_id) is most definitely absolute
> and unique - a primary key if you will. v$datafile.rfile# (or
> dba_data_files.relative_fno) is the column that can contain duplicates.
> Seems like you were saying it's the other way around? Or maybe I just
> misunderstood.
Just for you, I've just created an extra 1045 or so tablespaces in my
database (hope you appreciate the speed at which I type!). For the
(unimaginitive!) record, tablespace T21 uses datafile T21.DBF and is the
21st datafile in my database. Likewise, tablespace T1045 uses datafile
T1045.DBF and is the 1045th datafile in my database.
SQL> select count(*) from v$tablespace;
COUNT(*)
----------
1072
The crucial bit happens, of course, around the 1023rd datafile:
SQL> select file#, rfile# from v$datafile
2 where file#>1020;
FILE# RFILE#
---------- ----------
1021 1021
1022 1022
1023 1023
1024 1
1025 2
1026 3
1027 4
Which just goes to prove that sometimes I can't remember things the right
way around, and sometimes I just get them plain wrong: 102*3* is the right
number of data files, not 1022.
Anyway: try this:
SQL> create table dino
2 (col1 char(5))
3 tablespace T1024;
SQL> insert into dino values ('abcd');
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_relative_fno(rowid) from dino;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
1
So table DINO, which was created in tablespce T1024, which we know has
relative file number 1, contains rows which also have 1 stored as part of
their rowid.
Hope that clarifies.
Regards
HJR
Sean M
>
>
> Pedantically, and technically, I'm talking about the file number component
> of the ROWID, which will match with the relative file number visible in
> whatever view you care to look at. A worthwhile clarification, so thanks.
>
<Major Snippage>
> Hope that clarifies.
Crystal, thank you.
Regards,
Sean
Richard Foote
> This is normal. The relative_fno (as well as the header_file column) in
> dba_segments view indicates the file number of the datafile that the
> segment header resides, and as you know one segment can span more than one
> data file. Those "missing" FNO in the view simply means those datafiles
> have no segment header - they are occupied by the "body" of data segments
> whose header is elsewhere (in other FNOs). If you really want to get a full
> list of FNOs (for datafiles that has data in them), you can check the
> dba_extents view - go one step further in a more detail level. A
> select distinct relative_fno from dba_extents will get you a full list
> of FNOs as you see from dba_data_files (as long as every datafile has data,
> i.e. extents, in it).
>
Hi Jan,
Just to add to what Xuequn has said, note that Oracle places the first
extent (which obviously contains the segment header) into the first
data file listed in the tablespace (based on rfn). This means that
unless the first data file is full (or sufficiently full not to be
able to cope with the first extent size), all segment headers will be
found in this first datafile, hence giving you this result.
Scale away !!
Richard
Richard Foote
news:JUoV9.25118$jM5....@newsfeeds.bigpond.com...
> Which just goes to prove that sometimes I can't remember things the right
> way around, and sometimes I just get them plain wrong: 102*3* is the right
> number of data files, not 1022.
>
Hi again Howard,
I 'think' the 1022 is O/S dependent.
I've a question I hope you might be able to answer. Do you have any ideas on
why Oracle chose to add the object id portion to the rowid and make the file
number "relative" rather than simply allowing the file number portion bit to
be bigger.
I believe the answer has to do with efficiencies in how it deals with
partitioned objects but I've yet to be convinced that it's merits or
otherwise.
Cheers
Richard
Howard J. Rogers
"Richard Foote" <richar...@bigpond.com> wrote in message
news:lCtV9.25253$jM5....@newsfeeds.bigpond.com...
> "Howard J. Rogers" <howard...@yahoo.com.au> wrote in message
> news:JUoV9.25118$jM5....@newsfeeds.bigpond.com...
>
> > Which just goes to prove that sometimes I can't remember things the
right
> > way around, and sometimes I just get them plain wrong: 102*3* is the
right
> > number of data files, not 1022.
> >
>
> Hi again Howard,
>
> I 'think' the 1022 is O/S dependent.
Oh, well that might explain the confusion then. It says in the 8.0 DBA
courseware 1023. Taidg corrected that many moons ago to 1022. In the 8i
courseware I believe on one page it said 1022 and on another 1023. The
self-contradictory nature of the beast caused me to revert to believing
Taidg.
>
> I've a question I hope you might be able to answer. Do you have any ideas
on
> why Oracle chose to add the object id portion to the rowid and make the
file
> number "relative" rather than simply allowing the file number portion bit
to
> be bigger.
Well, as I posted... my understanding was that increasing the size of the
file number component would simply have meant there was a huge
incompatibility between 7 and 8.0. Migration from 7 to 8 would have had to
adjust every single rowid stored in indexes, for example. And can you
imagine the number of migrations that would have failed half-way through
when the database suddenly ran out of index tablespace space? or hit
maxextents for an index segment? Not pretty. Besides which, certain
badly-behaved applications (mentioning no names but SAP springs to mind)
which hard-coded the old-fashioned rowids into their imposing demonstrations
of how-not-to-code-an-app would simply have failed to work after the
upgrade. And they couldn't take that chance.
>
> I believe the answer has to do with efficiencies in how it deals with
> partitioned objects but I've yet to be convinced that it's merits or
> otherwise.
>
Could be, I suppose. Partitioning came in at the same time as the new rowid
format, so maybe there's a relationship after all.
What got me was why they simply didn't put TS# as part of the rowid instead
of the object#, which is effectively only a proxy for the tablespace number
in the first place. Would have been much simpler all round, I'd have
thought.
Life's Little Mystery 1022.
Regards
HJR
> Cheers
>
> Richard
>
>
>
>
Jan Gelbrich
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" <howard...@yahoo.com.au> schrieb im Newsbeitrag
news:ANmV9.25033$jM5....@newsfeeds.bigpond.com...
Jan Gelbrich
Now I know where to go further - great !
Jan =)
"Richard Foote" <richar...@bigpond.com> schrieb im Newsbeitrag
news:69f6c1c8.03011...@posting.google.com...