4 Replies Latest reply: May 19, 2012 6:31 AM by user639304 RSS

    File_id and relative_fno in dba_data_files

    user639304
      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
          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....
            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
              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
                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