The following data dictionary views provide useful information about the datafiles of a database:
||Provides descriptive information about each datafile, including the tablespace to which it belongs and the file ID. The file ID can be used to join with other views for detail information.|
||Contains datafile information from the control file|
||Contains information from datafile headers|
This example illustrates the use of one of these views,
SELECT NAME, FILE#, STATUS, CHECKPOINT_CHANGE# "CHECKPOINT" FROM V$DATAFILE; NAME FILE# STATUS CHECKPOINT -------------------------------- ----- ------- ---------- /u01/oracle/rbdb1/system01.dbf 1 SYSTEM 3839 /u02/oracle/rbdb1/temp01.dbf 2 ONLINE 3782 /u02/oracle/rbdb1/users03.dbf 3 OFFLINE 3782
FILE# lists the file number of each datafile; the first datafile in the
SYSTEM tablespace created with the database is always file 1.
STATUS lists other information about a datafile. If a datafile is part of the
SYSTEM tablespace, its status is
SYSTEM (unless it requires recovery). If a datafile in a non-
SYSTEM tablespace is online, its status is
ONLINE. If a datafile in a non-
SYSTEM tablespace is offline, its status can be either
CHECKPOINT lists the final SCN (system change number) written for the most recent checkpoint of a datafile.
See Also:Oracle Database Reference for complete descriptions of these views