Implementing Oracle Managed Files (OMF)

Oracle 9i has the OMF feature that assists with the management and maintenance of database files.

One new initialization parameter is needed when you are using OMF with tablespaces in the database, DB_CREATE_FILE_DEST. You set this parameter to provide the default location for data files. The parameter, of course, can be overridden in a CREATE TABLESPACE statement. You can either set this parameter directly in the parameter file (initSID.ora), or it can be set dynamically through the use of the ALTER SYSTEM command:

 ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/mydatabases/mydb1'; 

After your database is configured to use OMF, the DATAFILE clause of the CREATE TABLESPACE command is no longer necessary. The data file is created in the file system or directory specified by the DB_CREATE_FILE_DEST parameter in the PFILE or the SPFILE. DB_CREATE_FILE_DEST defines the default file system directory location where Oracle creates data files or tempfiles when no file specification is given in the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE operation. This location is also used as the default file system directory for online redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

By default, files are 100MB in size unless otherwise specified and also have AUTOEXTEND turned on with unlimited restriction on the data file.

When a tablespace is dropped when you are using OMF, you don't have to specify the AND DATAFILES option; the data files are dropped automatically.

DB_CREATE_ONLINE_LOG_DEST_n should be also set so that you can deliberately control where Oracle puts not only the data files but the online redo log files and the control files as well. Simply setting DB_CREATE_FILE_DEST does not control these other files.

Now that we have established the different kinds of tablespaces and how to create, delete, and maintain your tablespaces, it would be good to know where you can go to find information about the tablespaces in your database.