Datafiles typically comprise the largest part of any Oracle database, at least in terms of the disk space used.
Datafiles are not where Oracle stores your data. You can get a quick list of all the datafiles in your database and their sizes, by querying the V$DATAFILE view.
The following example shows how to do this from SQL*Plus:
SQL> COLUMN name FORMAT A35
SQL> COLUMN status FORMAT A6
SQL> COLUMN bytes FORMAT 999,999,999
SQL> SELECT name, status, bytes
2 FROM v$datafile;
NAME STATUS BYTES
-------------------------------- ------ ------------
C:\ORANT\DATABASE\SYS1ORCL.ORA SYSTEM 31,457,280
C:\ORANT\DATABASE\USR1ORCL.ORA ONLINE 3,145,728
C:\ORANT\DATABASE\RBS1ORCL.ORA ONLINE 5,242,880
C:\ORANT\DATABASE\TMP1ORCL.ORA ONLINE 2,097,152
Display of the Datafile View
If you do not mind a display with long lines that wrap around the screen, you can omit the COLUMN
commands and just issue the SELECT statement. The STATUS column tells you whether the datafile is currently open for use, and also indicates whether a datafile belongs to the system tablespace. A status of ONLINE tells you that the file is open. A status of SYSTEM indicates that the file belongs to the system tablespace. The system tablespace contains the data dictionary, and is always online as long as an Oracle database is open.
The BYTES column tells you how large the datafile is. In this example, the SYS1ORCL.ORA file is approximately 31 megabytes in size.
How the Oracle Database File Mapping Interface Works
This section describes the components of the Oracle Database file mapping interface and how the interface works. It contains the following topics:
The following sections briefly describes these components and how they work together to populate the mapping views:
FMON FMON is a background process started by the database whenever the FILE_MAPPING initialization parameter is set to TRUE.
FMON is responsible for:
Building mapping information, which is stored in the SGA. This information is composed of the following structures:
File system extents
Refreshing mapping information when a change occurs because of:
a) Changes to data files (size), b) Addition or deletion of data files, c) Changes to the storage configuration (not frequent)
Saving mapping information in the data dictionary to maintain a view of the information that is persistent across startup and shutdown operations
Restoring mapping information into the SGA at instance startup. This avoids the need for a potentially expensive complete rebuild of the mapping information on every instance startup. You help control this mapping using procedures that are invoked with the DBMS_STORAGE_MAP package.