Oracle Corporation offers specific recommendations on the naming and placement of database files to help you separate the different types of files in an Oracle installation.
This eases administration and spreads I/O across as many devices as possible. The Oracle database software, the control files and the datafiles, and the administrative files are all stored in separate directory trees and can be moved around independently of each other.
Oracle refers to their recommendations as the Optimal Flexible Architecture (OFA).
Following OFA creates a directory structure like the one below.
Move your mouse over the following diagram for more explanations.
The Oracle Managed File (OMF) feature automates many aspects of tablespace management, such as file placement, naming, and sizing. You control OMF by setting the following initialization parameters:
If you set these parameters before you create the database, Oracle uses them for the placement of the data files, control files, and online redo logs. You can also enable OMF after your database has been created.
Oracle uses the values of the initialization parameters for the locations of any newly added files. Oracle also determines the name of the newly added file.
The advantage of using OMF is that creating tablespaces is simplified. For example, the CREATE TABLESPACE statement does not need to specify anything other than the tablespace name.
First, enable the OMF feature by setting the DB_CREATE_FILE_DEST parameter:
SQL> alter system set db_create_file_dest='/u01';
Now, issue the CREATE TABLESPACE statement:
SQL> create tablespace inv1;
This statement creates a tablespace named INV1, with a default data file size of 100MB. Keep in mind that you can override the default size of 100MB by specifying a size:
SQL> create tablespace inv2 datafile size 20m;
To view the details of the associated data files, query the V$DATAFILE view, and note that Oracle has created subdirectories beneath the /u01 directory and named the file with the OMF format:
SQL> select name from v$datafile where name like '%inv%';
One limitation of OMF is that you are limited to one directory for the placement of data files. If you want to add data files to a different directory, you can alter the location dynamically:
SQL> alter system set db_create_file_dest='/u02';
Although this procedure is not a huge deal, I find it easier not to use OMF. Most of the environments I have worked in have many mount points assigned for database use.
You do not want to have to modify an initialization parameter every time you need a data file added to a directory that is not in the current definition of DB_CREATE_FILE_DEST.
It is easier to issue a CREATE TABLESPACE statement or ALTER TABLESPACE statement that has the file location and storage parameters in the script.
It is not cumbersome to provide directory names and file names to the tablespace-management statements.