When you see the following message in the alert log, ever wonder how the file number for a temp data file could be 2050 as I was?

ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 2050 (block # 322176)
ORA-01110: data file 2050: '/dbase/PROD/temp2.dbf'
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 322176
Additional information: 4294967295

Looking at v$tempfile, I could see the file number is 2. Where does the number 2050 come from?

10:57:05 SQL> select file#, ts#, name from  v$tempfile where name like '%temp2%';

     FILE#        TS# NAME
---------- ---------- --------------------------------------------------
         2          3 /dbase/PROD/temp2.dbf

And if you are like me, need to check temporary tablespace usage from time to time, you might have also noticed values from the column segfile# of v$tempseg_usage and v$sort_usage are quite bigger than the file_id of temp data files from dba_tempfiles (or file# from v$tempfile).

So how does Oracle come up with this?

Well, since temp data files are quite different than other normal data files and not needed for media recovery, Oracle treats them differently. The segfile# from v$sort_usage ( v$tempseg_usage) corresponds to the sum of the value for parameter db_files and the value for file# from v$tempfile.

For my system, I have the parameter db_files set to 2048, that why I saw 2050 (2048 +2) for the temp2.dbf which has file# 2 in v$tempfile.

21:14:34 SQL> show parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     2048

21:18:58 SQL>  select file#, ts#, name from  v$tempfile where file#=(2050-2048);

     FILE#        TS# NAME
---------- ---------- ----------------------------------------
         2         3 /dbase/PROD/temp2.dbf

Isn’t it look like an obvious answer? Although I knew which temp datafiles to check, the question had bothered me quite some time until I came across the article “Unable To Drop Temporary Files – ORA-25152 (Doc ID 738030.1)” recently — the equation is mentioned there!

• Oracle