Oracle 视图 V$DATAFILE 官方解释,作用,如何使用详细说明

本站中文解释

一、关于Oracle视图
Oracle视图是Oracle数据库中只读的逻辑表,由一张或多张物理表的行和列组成,它可以被认为是一种物理表的虚拟映射,具有非常强大的功能。

二、V$DATAFILE视图介绍
V$DATAFILE视图显示实例归属的每个数据文件的当前信息。该视图存储在SGA中,运行中的实例可以实时查看数据库的信息,提供数据库实例浏览。

三、用途及如何使用
V$DATAFILE视图可以用来查看数据文件的名称,大小,当前状态等信息,查看数据文件是否正常工作。

通过Oracle之类SELECT * FROM V$DATAFILE 查看所得到的视图信息,若要更加有效的获取数据文件信息,可以使用Alter Database Command 命令:
例如:ALTER DATABASE DATAFILE ‘c:\oracle\oradata\mydb\mydata.dbf’ RESIZE 5M; 会把文件大小调整成5m,并强制运行中的数据库实例 应用该变更。

官方英文解释

V$DATAFILE displays datafile information from the control file.

See Also:

“V$DATAFILE_HEADER”, which displays information from data file headers

Column Datatype Description

FILE#

NUMBER

Absolute file number

CREATION_CHANGE#

NUMBER

Change number at which the data file was created

CREATION_TIME

DATE

Timestamp of the data file creation

TS#

NUMBER

Tablespace number

RFILE#

NUMBER

Tablespace relative data file 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 allowed

  • READ ONLY – No SQL updates allowed

  • READ WRITE – Full access allowed

  • UNKNOWN – Unknown whether SQL updates would be allowed or not

CHECKPOINT_CHANGE#

NUMBER

SCN at last checkpoint

CHECKPOINT_TIME

DATE

Timestamp of the checkpoint#

UNRECOVERABLE_CHANGE#

NUMBER

Last unrecoverable change number made to this data file. 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 data file (null if the data file 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 data file 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 data file size (in bytes); 0 if inaccessible

BLOCKS

NUMBER

Current data file size (in blocks); 0 if inaccessible

CREATE_BYTES

NUMBER

Size when created (in bytes)

BLOCK_SIZE

NUMBER

Block size of the data file

NAME

VARCHAR2(513)

Name of the data file

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 (check in standby database)

FIRST_NONLOGGED_TIME

DATE

First nonlogged time (check in standby database)

FOREIGN_DBID

NUMBER

Foreign DBID from which this data file came from. The value is 0 if this file is not a foreign database file.

FOREIGN_CREATION_CHANGE#

NUMBER

Creation SCN of a foreign data file. The value is 0 if this file is not a foreign database file.

FOREIGN_CREATION_TIME

DATE

Creation time of a foreign data file. The value is 0 if this file is not a foreign database file.

PLUGGED_READONLY

VARCHAR2(3)

YES if this is a transported read-only foreign file; otherwise NO.

PLUGIN_CHANGE#

NUMBER

SCN at which the foreign data file was transported into the database. The value is 0 if this file is not a foreign database file.

PLUGIN_RESETLOGS_CHANGE#

NUMBER

The SCN of the RESETLOGS operation for the incarnation into which this foreign file was transported. The value is 0 if this file is not a foreign database file.

PLUGIN_RESETLOGS_TIME

DATE

The time of the RESETLOGS operation for the incarnation into which this foreign file was transported. The value is 0 if this file is not a foreign database file.

CON_ID

NUMBER

The ID of the container to which the data pertains. Possible values include:

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.

  • 1: This value is used for rows containing data that pertain to only the root

  • n: Where n is the applicable container ID for the rows containing data


数据运维技术 » Oracle 视图 V$DATAFILE 官方解释,作用,如何使用详细说明