Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E17120-11
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

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 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 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.


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.

See Also:

Oracle Database Reference for complete descriptions of these views