This discussion is archived
4 Replies Latest reply: May 19, 2012 4:31 AM by user639304 RSS

File_id and relative_fno in dba_data_files

user639304 Explorer
Currently Being Moderated
Hi,

I don't understand the difference between the columns file_id and relative_fno in dba_data_files.
Does someone have in his database a row in dba_data_files where the values of file_id and relative_fno are different ?

Thanks.

Edited by: user639304 on 19 mai 2012 04:01
  • 1. Re: File_id and relative_fno in dba_data_files
    Girish Sharma Guru
    Currently Being Moderated
    Check below link where Jonathan lewis has explained the difference between them :

    file_id is the absolute file number within
    the control file structure
    relative_fno is a file number within tablespace.
    It is slightly misleading to call it the 'relative file
    number' as Oracle has done, as this suggests
    that just about every relative file number should
    be 1 as the first file in the tablespace. In fact
    the number simply has to be unique within a
    tablespace.
    To maintain backwards compatibility with 7,
    Oracle has arranged the code for handling
    relative file numbers so that relative file number
    = absolute file number for the first 1022 (-ish) files
    in the database. However there is an event
    (10120 I think) that you can set at the database
    level if you want to experiment with relative file
    numbers that do not match absolute file numbers.


    https://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/2c58c6873353b829/551565333e29472%3Fq%3D%2522Eduardo%2BYanes%2522%230551565333e29472&ei=iGwTS6eaOpW8Qpmqic0O&sa=t&ct=res&cd=4&source=groups&usg=AFQjCNEmIAIpiJCvM9bh4mEc1alRV1ZLXg
  • 2. Re: File_id and relative_fno in dba_data_files
    Aman.... Oracle ACE
    Currently Being Moderated
    user639304 wrote:
    Hi,

    Does anyone have in his database a row in dba_data_files where the values of file_id and relative_fno are different ?
    This is certainly possible. What's your question?

    Aman....
  • 3. Re: File_id and relative_fno in dba_data_files
    user639304 Explorer
    Currently Being Moderated
    Hi Aman,

    I asked the question because in my database, for every row in dba_data_files, file_id and relative_fno have the same value.
  • 4. Re: File_id and relative_fno in dba_data_files
    Girish Sharma Guru
    Currently Being Moderated
    user639304 wrote:
    Hi Aman,

    I asked the question because in my database, for every row in dba_data_files, file_id and relative_fno have the same value.
    And in the below link where Tanel has showed why there is different file_id and RELATIVE_FNO for database which are >=8; i.e. databases which can have 1022 datafiles per tablespace not per database as previously. That’s why the 10-byte extended rowids were introduced, which also included the data object ID of a segment inside the rowid.

    As Aman has said, there are chances that file_id and relative_fno can be difference, if you are having imported transportable tablespace's data.

    http://blog.tanelpoder.com/2008/10/21/transportable-tablespaces-and-rowid-uniqueness/

    Regards
    Girish Sharma

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points