Differences between absolute and relative file numbers in Oracle

I. Official Oracle description
 
Oracle Database assigns each datafile two associated file numbers, an absolute file

Number and a relative file number, that are used to uniquely identify it
 
 
Absolute: Uniquely identifies a datafile in the database. this file number can be used in your SQL statements that reference datafiles in place of using the file name. the absolute file number can be found in the FILE # column of the V $ DATAFILE or V $ TEMPFILE view, or in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view
 
 
Relative: Uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when

Number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number. In a bigfile tablespace, the relative file number is always

1024 (4096 on OS/390 platform ).
 
Ii. Summary:
 
Summary: The oracle Database assigns an absolute file number and a relative file number to each newly created datafile. Both are used to uniquely identify the data file, except that the identification range is different.
 
Absolute file number:
 
1. The absolute file number uniquely identifies a data file within the database range
 
2 in SQL statements, the absolute file number is often used to replace the name of the data file.
 
3. You can obtain the absolute file number by querying the view v $ datafile | v $ tempfile | dba_data_files | dba_temp_files related field file # | file_id.
 
For example, the file # field of v $ datafile
 
Select file #, name, status from v $ datafile
 
FILE # NAME STATUS
 
---------------------------------------------------------
 
1/oracle/CRM2/system1.dbf SYSTEM
 
2/oracle/CRM2/zxb. dbf ONLINE
 
3/oracle/CRM2/CRM/sysaux01.dbf ONLINE
 
4/oracle/CRM2/CRM/users01.dbf ONLINE
 
5/oracle/CRM2/zxa. dbf ONLINE
 
6/oracle/CRM2/CRM/test1.dbf ONLINE
 
7/oracle/CRM2/zxc. dbf ONLINE
 
8/oracle/CRM2/CRM/undotbs1.dbf ONLINE
 
Equivalent file number:
 
1 The relative file number uniquely identifies a data file within the tablespace.
 
2. There are no more than 1023 data files in the database. The relative file number is equal to the absolute file number, and the opposite is different.
 
3. In a large table space, the relative file number of the data file is always 1024.
 
4. query the v $ datafile | v $ tempfile field RFILE #, dba_data_files | dba_temp_files field RELATIVE_FNO and so on to find the relative file number.
 
 
 
Example 1 v $ datafile field rfile #
 
SQL> Select file #, rfile #, name, status from v $ datafile;
 
 
 
FILE # RFILE # NAME STATUS
 
-------------------------------------------------------------------
 
1 1/oracle/CRM2/system1.dbf SYSTEM
 
2 2/oracle/CRM2/zxb. dbf ONLINE
 
3 3/oracle/CRM2/CRM/sysaux01.dbf ONLINE
 
4 4/oracle/CRM2/CRM/users01.dbf ONLINE
 
5 5/oracle/CRM2/zxa. dbf ONLINE
 
6 6/oracle/CRM2/CRM/test1.dbf ONLINE
 
7 7/oracle/CRM2/zxc. dbf ONLINE
 
8 8/oracle/CRM2/CRM/undotbs1.dbf ONLINE
 
 
 
8 rows selected.
 
Example 2 absolute and relative file numbers of large table Spaces
 
 
 
SQL> select file #, rfile #, name, status from v $ datafile;
 
 
 
FILE # RFILE # NAME STATUS
 
-------------------------------------------------------------------
 
1 1/oracle/CRM2/system1.dbf SYSTEM
 
2 2/oracle/CRM2/zxb. dbf ONLINE
 
3 3/oracle/CRM2/CRM/sysaux01.dbf ONLINE
 
4 4/oracle/CRM2/CRM/users01.dbf ONLINE
 
5 5/oracle/CRM2/zxa. dbf ONLINE
 
6 6/oracle/CRM2/CRM/test1.dbf ONLINE
 
7 7/oracle/CRM2/zxc. dbf ONLINE
 
8 8/oracle/CRM2/CRM/undotbs1.dbf ONLINE
 
9 1024/oracle/CRM2/CRM/zxbig. dbf ONLINE
 
 
 
SQL> select file_id, relative_fno, file_name from dba_data_files order by 1
 
FILE_ID RELATIVE_FNO FILE_NAME
 
--------------------------------------------------------------
 
1/oracle/CRM2/system1.dbf
 
2 2/oracle/CRM2/zxb. dbf
 
3 3/oracle/CRM2/CRM/sysaux01.dbf
 
4 4/oracle/CRM2/CRM/users01.dbf
 
5 5/oracle/CRM2/zxa. dbf
 
6 6/oracle/CRM2/CRM/test1.dbf
 
7 7/oracle/CRM2/zxc. dbf
 
8 8/oracle/CRM2/CRM/undotbs1.dbf
 
9 1024/oracle/CRM2/CRM/zxbig. dbf