Datafile

Overview of Data files
    
    A table space in an Oracle database consists of one or more physical datafiles. A datafile can be associated with only one tablespace and only one database.

At least one datafile is required for the SYSTEM and SYSAUX tablespaces of a database.

Your database should contain several other tablespaces with their associated datafiles or tempfiles.

Initialization Parameter: db_files

    When starting an Oracle Database instance, the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This limit applies for the life of the instance. You can change the value of DB_FILES (by changing the initialization parameter setting), but the new value does not take effect until you shut down and restart the instance.

When you check parameter db_files, you will see that it matches the amount of datafiles inside the database:

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

db_files is set the 200 (default value), exactly the same as the amount of datafiles inside the database:

SQL> select count(*) from dba_data_files;

  COUNT(*)
----------
         4


    If the db_files parameter is set the 200 (default value), exactly the same as the amount of dba_data_files inside the database also 200 then we can't add the datafiles as well as we can't create the tablespace.

if we try to create tablespace we can get this error ORA-00059: maximum number of DB_FILES exceeded


When determining a value for DB_FILES, take the following into consideration:

If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit without first shutting down the database.
If the value of DB_FILES is too high, memory is unnecessarily consumed.


DBA_DATA_FILES: information about each datafile, including the tablespace to which it belongs and the file ID.

select * from dba_data_files;

DBA_EXTENTS: Contains the file ID of the datafile containing the extent.

DBA_FREE_SPACE: describes the free extents in all tablespaces in the database.

V$DATAFILE: Contains datafile information from the control file.

V$DATAFILE_HEADER: Contains information from datafile headers


Creating Datafiles and Adding Datafiles to a Tablespace:

You can create datafiles and associate them with a tablespace using any of the statements listed in the following.

SQL Statement:

CREATE TABLESPACE  ---  Creates a tablespace and the datafiles that comprise it

CREATE TEMPORARY TABLESPACE --- Creates a locally-managed temporary tablespace and the tempfiles (tempfiles are a special kind of datafile) that comprise it.

ALTER TABLESPACE ... ADD DATAFILE  ---   Creates and adds a datafile to a tablespace.

ALTER TABLESPACE ... ADD TEMPFILE ---    Creates and adds a tempfile to a temporary tablespace.

CREATE DATABASE ----  Creates a database and associated datafiles.

ALTER DATABASE ... CREATE DATAFILE ----- Creates a new empty datafile in place of an old one--useful to re-create a data file that was lost with no backup.