Re: Data Dictionary: relative_fno question

"Sean M" <smckeown_at_adelphia.net> wrote in message news:3E260C0E.8AEB61D_at_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
>
> 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
Received on Wed Jan 15 2003 - 20:46:46 CST