Oracle Scratchpad

June 7, 2012

File ID

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 5:31 pm BST Jun 7,2012

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.

2 Comments »

  1. Hi Jonathan,

    I suppose it might be reserved for temp files.

    HELLODBA.COM>show parameter db_files
    
    NAME                                 TYPE                             VALUE
    ------------------------------------ -------------------------------- -----------
    db_files                             integer                          200
    
    HELLODBA.COM>select file_id, file_name, tablespace_name, relative_fno from dba_data_files;
    
       FILE_ID FILE_NAME                                          TABLESPACE_NAME                RELATIVE_FNO
    ---------- -------------------------------------------------- ------------------------------ ------------
             4 C:\ORACLE11GR2\ORADATA\ORA11R2\USERS01.DBF         USERS                                     4
             3 C:\ORACLE11GR2\ORADATA\ORA11R2\UNDOTBS01.DBF       UNDOTBS1                                  3
             2 C:\ORACLE11GR2\ORADATA\ORA11R2\SYSAUX01.DBF        SYSAUX                                    2
             1 C:\ORACLE11GR2\ORADATA\ORA11R2\SYSTEM01.DBF        SYSTEM                                    1
             5 C:\ORACLE11GR2\ORADATA\ORA11R2\DEMO_DATA.DBF       DEMO_DATA                                 5
             6 C:\ORACLE11GR2\ORADATA\ORA11R2\DEMO_INDX.DBF       DEMO_INDX                                 6
             7 C:\ORACLE11GR2\ORADATA\ORA11R2\DFILE1.DAT          MULTIFILE_DATA                            7
             8 C:\ORACLE11GR2\ORADATA\ORA11R2\DFILE2.DAT          MULTIFILE_DATA                            8
             9 C:\ORACLE11GR2\ORADATA\ORA11R2\DFILE3.DAT          MULTIFILE_DATA                            9
            10 C:\ORACLE11GR2\ORADATA\ORA11R2\DFILE4.DAT          MULTIFILE_DATA                           10
            11 C:\ORACLE11GR2\ORADATA\ORA11R2\DFILE5.DAT          MULTIFILE_DATA                           11
            12 C:\ORACLE11GR2\ORADATA\ORA11R2\DFILE6.DAT          MULTIFILE_DATA                           12
    
    12 rows selected.
    
    HELLODBA.COM>select file_id, file_name, tablespace_name, relative_fno from dba_temp_files;
    
       FILE_ID FILE_NAME                                          TABLESPACE_NAME                RELATIVE_FNO
    ---------- -------------------------------------------------- ------------------------------ ------------
             1 C:\ORACLE11GR2\ORADATA\ORA11R2\TEMP01.DBF          TEMP                                      1
    
    While, in a 10046 trace file of disk sorting, we can find the absolute file number of temp files is RELATIVE_FNO+DB_FIELS
    
    WAIT #5: nam='direct path write temp' ela= 23472 file number=201 first dba=17398 block cnt=7 obj#=74177 tim=1800385730788
    WAIT #5: nam='direct path write temp' ela= 11994 file number=201 first dba=17405 block cnt=7 obj#=74177 tim=1800385743176
    WAIT #5: nam='direct path write temp' ela= 605 file number=201 first dba=17412 block cnt=7 obj#=74177 tim=1800385744045
    
    and from the fixed table (abstract from dba_temp_files) point to temp files, can find its absolute file number.
    
    HELLODBA.COM>select addr, tfnum, tfafn from sys.x$kcctf tf;
    
    ADDR          TFNUM      TFAFN
    -------- ---------- ----------
    0DB34304          1        201
    

    Regards,
    Fuyuncat

    Comment by Fuyuncat — June 8, 2012 @ 12:51 am BST Jun 8,2012 | Reply

  2. Reblogged this on lava kafle kathmandu nepal.

    Comment by lkafle — June 8, 2012 @ 2:46 am BST Jun 8,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,173 other followers