About file # In v $ datafile and v $ tempfile #,

Source: Internet
Author: User

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 fromSYSTEMTablespace)
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 inARCHIVELOGMode, then this column is updated when an unrecoverable operation completes. If the database is not inARCHIVELOGMode, this column does not get updated.
UNRECOVERABLE_TIME DATE Timestamp of the last unrecoverable change. This column is updated only if the database is inARCHIVELOGMode.
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 );0If 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 is1If the tablespace is plugged in and has not been made read/write,0If 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 !!




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.