Interim files file# and DB in Oracle

In the analysis of the relationship between V$tempfile and V$sort_usage, we notice that Oracle's allocation rules and regular data files are not the same for the absolute file number (AFN) of the temporary file.

In fact, the absolute file number of the temporary file should be equal to Db_files + file#.

Let's take a look at the example:SQL> select indx,tfnum,tfafn,tfcsz
2　from x$kcctf;
INDX　　　TFNUM　　　TFAFN　　　TFCSZ
---------- ---------- ---------- ----------
0　　　　　1　　　　201　　　 2560
SQL> show parameter db_files
NAME　　　　　　　　　　　　　　　　 TYPE　　　　VALUE
------------------------------------ ----------- ------
db_files　　　　　　　　　　　　　　 integer　　 200
SQL> select file#,name from v$tempfile;
FILE# NAME
--------- -----------------------------------------
1 +ORADG/danaly/tempfile/temp.267.600173887
SQL>

Therefore, it is not certain that v$tempfile.file# is defined as the absolute file number in an Oracle document. Often, we may see an error similar to the following in the alert log file:

Corrupt block relative dba: 0x00c0008a (file 202, block 138)
Bad header found during buffer read
Data in bad block -
type: 8 format: 2 rdba: 0x0140008a
last change scn: 0x0000.431f8beb seq: 0x1 flg: 0x08
consistency value in tail: 0x8beb0801
check value in block header: 0x0, block checksum disabled
spare1: 0x0, spare2: 0x0, spare3: 0x0

The file 202 here actually refers to temporary files.