About file # In v $ datafile and v $ tempfile #,
The v $ datafile view stores information about data files, and the v $ tempfile view stores information about temporary files. There are file # Fields in both views. Let's take a look at the definition of the official document:
V $ DATAFILE
This view contains datafile information from the control file.
See Also:
"V $ DATAFILE_HEADER", which displays information from datafile headers
Column |
Datatype |
Description |
FILE# |
NUMBER |
File identification number |
CREATION_CHANGE# |
NUMBER |
Change number at which the datafile was created |
CREATION_TIME |
DATE |
Timestamp of the datafile creation |
TS# |
NUMBER |
Tablespace number |
RFILE# |
NUMBER |
Tablespace relative datafile number |
STATUS |
VARCHAR2(7) |
Type of file (system or user) and its status. Values:OFFLINE ,ONLINE ,SYSTEM ,RECOVER ,SYSOFF (An offline file fromSYSTEM Tablespace) |
ENABLED |
VARCHAR2(10) |
Describes how accessible the file is from SQL:
DISABLED -No SQL access allowed
READ ONLY -No SQL updates allowed
READ WRITE -Full access allowed
UNKNOWN -Shocould not occur unless the control file is already upted
|
CHECKPOINT_CHANGE# |
NUMBER |
SCN at last checkpoint |
CHECKPOINT_TIME |
DATE |
Timestamp of the checkpoint # |
UNRECOVERABLE_CHANGE# |
NUMBER |
Last unrecoverable change number made to this datafile. If the database is inARCHIVELOG Mode, then this column is updated when an unrecoverable operation completes. If the database is not inARCHIVELOG Mode, this column does not get updated. |
UNRECOVERABLE_TIME |
DATE |
Timestamp of the last unrecoverable change. This column is updated only if the database is inARCHIVELOG Mode. |
LAST_CHANGE# |
NUMBER |
Last change number made to this datafile (null if the datafile is being changed) |
LAST_TIME |
DATE |
Timestamp of the last change |
OFFLINE_CHANGE# |
NUMBER |
Offline change number of the last offline range. This column is updated only when the datafile is brought online. |
ONLINE_CHANGE# |
NUMBER |
Online change number of the last offline range |
ONLINE_TIME |
DATE |
Online timestamp of the last offline range |
BYTES |
NUMBER |
Current datafile size (in bytes );0 If inaccessible |
BLOCKS |
NUMBER |
Current datafile size (in blocks); 0 if inaccessible |
CREATE_BYTES |
NUMBER |
Size when created (in bytes) |
BLOCK_SIZE |
NUMBER |
Block size of the datafile |
NAME |
VARCHAR2(513) |
Name of the datafile |
PLUGGED_IN |
NUMBER |
Describes whether the tablespace is plugged in. The value is1 If the tablespace is plugged in and has not been made read/write,0 If not. |
BLOCK1_OFFSET |
NUMBER |
Offset from the beginning of the file to where the Oracle generic information begins. The exact length of the file can be computed as follows:BYTES + BLOCK1_OFFSET . |
AUX_NAME |
VARCHAR2(513) |
Auxiliary name that has been set for this fileCONFIGURE AUXNAME |
FIRST_NONLOGGED_SCN |
NUMBER |
First nonlogged SCN |
FIRST_NONLOGGED_TIME |
DATE |
First nonlogged time |
V $ TEMPFILE
This view displays tempfile information.
Column |
Datatype |
Description |
FILE# |
NUMBER |
Absolute file number |
CREATION_CHANGE# |
NUMBER |
Creation System Change Number (SCN) |
CREATION_TIME |
DATE |
Creation time |
TS# |
NUMBER |
Tablespace number |
RFILE# |
NUMBER |
Relative file number in the tablespace |
STATUS |
VARCHAR2(7) |
Status of the file (OFFLINE |ONLINE ) |
ENABLED |
VARCHAR2(10) |
Enabled for read and/or write |
BYTES |
NUMBER |
Size of the file in bytes (from the file header) |
BLOCKS |
NUMBER |
Size of the file in blocks (from the file header) |
CREATE_BYTES |
NUMBER |
Creation size of the file (in bytes) |
BLOCK_SIZE |
NUMBER |
Block size for the file |
NAME |
VARCHAR2(513) |
Name of the file |
From the preceding document, we can see that in v $ tempfile and v $ datafile, file # indicates that the absolute file is good, while rfile # indicates the relative file number. Here we will verify it:
SQL> l 1* select file#,name from v$tempfileSQL> / FILE# NAME---------- ------------------------------------------------------------ 1 /home/app/oraten/oradata/oraten/temp01.dbf
SQL> select file#,name from v$datafile; FILE# NAME---------- ------------------------------------------------------------ 1 /home/app/oraten/oradata/oraten/system01.dbf 2 /home/app/oraten/oradata/oraten/undotbs01.dbf 3 /home/app/oraten/oradata/oraten/sysaux01.dbf 4 /home/app/oraten/oradata/oraten/users01.dbf 5 /home/app/oraten/oradata/oraten/test01.dbf
Both v $ datafile and v $ tempfile have files with absolute file number 1, which is obviously incorrect.
We know that oracle will report an error and display information about the file in the error message when the file cannot be accessed. Next we will simulate the fault of the temporary file.
[Oraten @ yue oraten] $ chmod 000 temp01.dbf [oraten @ yue oraten] $ ll total usage 932180-rw-r ----- 1 oraten dba 7061504 13:59 control01.ctl-rw-r ----- 1 oraten dba 7061504 November 7 13:59 control02.ctl-rw-r ----- 1 oraten dba 7061504 November 7 13:59 control03.ctl-rw-r ----- 1 oraten dba 52429312 November 7 13:44 redo01.log-rw-r ----- 1 oraten dba 52429312 November 7 13:54 redo02.log-rw- r ----- 1 oraten dba 52429312 November 7 13:44 redo03.log-rw-r ----- 1 oraten dba 272637952 November 7 13:49 sysaux01.dbf-rw-r ----- 1 oraten dba 461381632 November 7 13:49 system01.dbf ---------- 1 oraten dba 20979712 November 7 13:39 temp01.dbf-rw-r ----- 1 oraten dba 10493952 November 7 13:44 test01.dbf-rw-r ----- 1 oraten dba 26222592 November 7 13:49 undotbs01.dbf-rw-r ----- 1 oraten dba 5251072 November 7 13:44 users01.dbf
Force start Database
SQL> startup forceORACLE instance started.Total System Global Area 134217728 bytesFixed Size 2094544 bytesVariable Size 88082992 bytesDatabase Buffers 37748736 bytesRedo Buffers 6291456 bytesDatabase mounted.Database opened.SQL>
Create a temporary table and insert data
SQL> insert into t1 select * from user_tables;insert into t1 select * from user_tables *ERROR at line 1:ORA-01157: cannot identify/lock data file 101 - see DBWR trace fileORA-01110: data file 101: '/home/app/oraten/oradata/oraten/temp01.dbf'
The temporary file cannot be accessed. The file number shown here is 101. Why?
Modify the db_files parameter to 200 and continue the experiment.
SQL> show parameter db_filesNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_files integer 100SQL> alter system set db_files=200 scope=spfile;System altered.SQL> startup forceORACLE instance started.Total System Global Area 134217728 bytesFixed Size 2094544 bytesVariable Size 88082992 bytesDatabase Buffers 37748736 bytesRedo Buffers 6291456 bytesDatabase mounted.Database opened.SQL> insert into t1 select * from user_tables;insert into t1 select * from user_tables *ERROR at line 1:ORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: '/home/app/oraten/oradata/oraten/temp01.dbf'
Originally, file # In v $ tempfile is not an absolute file number. The absolute file number of a temporary file is the file # + db_files parameter.
Manual completion !!