Datafiles Data Dictionary Views

The following data dictionary views provide useful information about the datafiles of a database:

View	Description
`DBA_DATA_FILES`	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.
`DBA_EXTENTS` `USER_EXTENTS`	`DBA` view describes the extents comprising all segments in the database. Contains the file ID of the datafile containing the extent. `USER` view describes extents of the segments belonging to objects owned by the current user.
`DBA_FREE_SPACE` `USER_FREE_SPACE`	`DBA` view lists the free extents in all tablespaces. Includes the file ID of the datafile containing the extent. `USER` view lists the free extents in the tablespaces accessible to the current user.
`V$DATAFILE`	Contains datafile information from the control file
`V$DATAFILE_HEADER`	Contains information from datafile headers

This example illustrates the use of one of these views, V$DATAFILE.

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 OFFLINE or RECOVER. CHECKPOINT lists the final SCN (system change number) written for the most recent checkpoint of a datafile.