File ID

There was a question on the OTN database forum a little while ago about the different between the relative and absolute files numbers for a data file as reported in view dba_data_files – where they appear as file_id and relative_fno – or v$datafile – where they appear as file# and rfile#. In response, someone resurrected a very old posting of mine from the Usenet newsgroup comp.databases.oracle.server, which says the following:

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.

I don’t think there’s much more that needs to be said on the topic – even after the 11 years that have passed since I wrote the note. But if you want to test whether some piece of code needs a relative file number or absolute file number, event 10120 is the correct event, so it’s easy to create a file where the two numbers don’t match:

alter session set events '10120 trace name context forever';

create
	tablespace test_8k
	datafile 'C:\ORACLE\ORADATA\D10g\test_8k.dbf'
	SIZE 129m reuse
	blocksize 8k
	extent management local
	uniform size 1M
	segment space management manual
;

alter session set events '10120 trace name context off';

select file#, rfile#, name from v$datafile;

     FILE#     RFILE# NAME
---------- ---------- --------------------------------------
         1          1 C:\ORACLE\ORADATA\D10G\SYSTEM01.DBF
         2          2 C:\ORACLE\ORADATA\D10G\UNDOTBS01.DBF
         3          3 C:\ORACLE\ORADATA\D10G\SYSAUX01.DBF
         4          4 C:\ORACLE\ORADATA\D10G\USERS01.DBF
         5          6 C:\ORACLE\ORADATA\D10G\TEST_8K.DBF

I do see code from time to time that uses the wrong file number, so whenever you write SQL that does something with file ids, it’s worth checking which of the two you’re supposed to use.

The notes that go with this little script include a couple of other options viz:

a) create more than 1022 files in the database because this is where the file# = rfile# (probably) starts to break

b) import a suitable tablespace from another database

The note on OTN includes a link to an entertaining blog about duplicate rowids  from Tanel Poder, by the way. Make sure you read the comments – especially the one from Stig.