v$datafile
v$datafile是从oracle的控制文件中获得的数据文件的信息
This view contains datafile information from the control file.
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 from the SYSTEM tablespace) |
ENABLED | VARCHAR2(10) | Describes how accessible the file is from SQL:DISABLED - No SQL access allowedREAD ONLY - No SQL updates allowedREAD WRITE - Full access allowedUNKNOWN - should not occur unless the control file is corrupted |
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 in ARCHIVELOG mode, then this column is updated when an unrecoverable operation completes. If the database is not in ARCHIVELOG 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 in ARCHIVELOG 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 is 1 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 file via CONFIGURE AUXNAME |
FIRST_NONLOGGED_SCN | NUMBER | First nonlogged SCN |
FIRST_NONLOGGED_TIME | DATE | First nonlogged time |
v$datafile_header
v$datafile_header是从数据文件的头部
V$DATAFILE_HEADER是Oracle数据库中的一个视图,它可以提供有关每个表空间数据文件的信息。可以回答的问题包括:每个表空间中有多少数据文件?每个数据文件的位置是什么?数据文件的大小?数据文件的状态是什么?
-- 1、查询每个表空间中有多少数据文件:
SELECT TABLESPACE_NAME, COUNT(*) FROM V$DATAFILE_HEADER GROUP BY TABLESPACE_NAME;
-- 2、查询每个数据文件的详细信息,包括位置、大小以及状态:
SELECT * FROM V$DATAFILE_HEADER;
Column | Datatype | Description |
---|---|---|
FILE# | NUMBER | Datafile number (from control file) |
STATUS | VARCHAR2(7) | ONLINE | OFFLINE (from control file) |
ERROR | VARCHAR2(18) | NULL if the datafile header read and validation were successful. If the read failed then the rest of the columns are NULL. If the validation failed then the rest of columns may display invalid data. If there is an error then usually the datafile must be restored from a backup before it can be recovered or used. |
FORMAT | NUMBER | Indicates the format for the header block. The possible values are 6, 7, 8, or 0. 6 - indicates Oracle Version 6 7 - indicates Oracle Version 7 8 - indicates Oracle Version 8 0 - indicates the format could not be determined (for example, the header could not be read) |
RECOVER | VARCHAR2(3) | File needs media recovery (YES | NO) |
FUZZY | VARCHAR2(3) | File is fuzzy (YES | NO) |
CREATION_CHANGE# | NUMBER | Datafile creation change# |
CREATION_TIME | DATE | Datafile creation timestamp |
TABLESPACE_NAME | VARCHAR2(30) | Tablespace name |
TS# | NUMBER | Tablespace number |
RFILE# | NUMBER | Tablespace relative datafile number |
RESETLOGS_CHANGE# | NUMBER | Resetlogs change# |
RESETLOGS_TIME | DATE | Resetlogs timestamp |
CHECKPOINT_CHANGE# | NUMBER | Datafile checkpoint change# |
CHECKPOINT_TIME | DATE | Datafile checkpoint timestamp |
CHECKPOINT_COUNT | NUMBER | Datafile checkpoint count |
BYTES | NUMBER | Current datafile size in bytes |
BLOCKS | NUMBER | Current datafile size in blocks |
NAME | VARCHAR2(513) | Datafile name |
SPACE_HEADER | VARCHAR2(40) | The amount of space currently being used and the amount that is free, as identified in the space header |
LAST_DEALLOC_SCN | VARCHAR2(16) | Last deallocated SCN |
FUZZY字段的含义
FUZZY bit in datafile header means that there may have been writes into a datafile after the last checkpoint. E.g. there may be changes written to datafile with higher SCN than checkpoint_change# stored in datafile header (seen from v$datafile_header.checkpoint_change#).
FUZYY表示模糊性,意思是,该数据文件处于模糊状态,在最近一次CHECKPOINT后,该文件上的数据可能被修改过了,但没来得及更新到该文件上(或者该文件不知道),需要读取日志信息来判断。
正常关闭数据库的终止SCN应该和启动SCN相同。FUZZY等于NO,且数据库的终止SCN等于启动SCN等于数据文件SCN,那么可以认为数据库是正常关闭,且在打开数据库之前不需要执行实例恢复或Crash恢复。
非正常关闭数据库实例,FUZZY字段的值是YES。
关系
在正常运行下,两者的检查点SCN值是一致的,但当datafile出现损坏时可以用v d a t a f i l e h e a d e r 确定应用重做的起始 S C N ,而 v datafile_header确定应用重做的起始SCN,而v datafileheader确定应用重做的起始SCN,而vdatafile可确定应用重做的结束SCN值
select a.name,a.checkpoint_change# "start_SCN",
b.checkpoint_change# "last_SCN"
from v$datafile_header a, v$datafile b
where a.file#=b.file#;