Oracle9i New Feature: Oracle Managed Files


Oracle Managed Files (OMF), a new feature introduced in Oracle9i, enables the Oracle Server to automatically create and delete database files using standard operating system interfaces. This
feature hugely simplifies the job of the DBA, as the DBA doesn't need to interact with the underlying operating system to create and delete files.

For OMF to work, the DBA can specify a single directory to hold data and temp files, and up to 5 directories for multiplexed redo log and control files. All files will be created with unique names, 100 MB in size, with properties AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED. The creation and removal of trace files, audit files, alert files, and core files are not affected by this feature.


  • DBAs don't need to specify file names, locations and sizes when creating a tablespace or database
  • Automatic removal of files when a tablespace or log file is dropped
  • Increased script portability as OS specific file names do not need to be hard coded
  • Simplified creation of test and development systems


  • Can only be used with file systems, not with RAW Volumes
  • Generated file names and locations might not be according to the site's naming standards
  • Limited scope for file placement and I/O tuning may impact performance (although locations can be altered dynamically)

Enabling Oracle Managed Files:

Set the following initialization parameters in your INIT.ORA or SPFILE and restart the database, or set them on-line using the "ALTER SYSTEM SET parm = value" command:


specifies where data files and temp files should be created. On-line redo logs and control files will also be created here if db_create_online_log_dest_n is not set.


specifies where on-line redo logs and control files should be created. You can specify up to 5 locations (n=1..5).


can be set to MANUAL or AUTO. If AUTO is specified, Oracle will automatically create files and drop tablespaces on the standby database as the redo log stream is applied to the standby database.

Naming Standard Used for Generated File Names:

The Oracle Server will generate file names according to the following rules.

  • OMF file names start with "o1_mf_"; and
  • OMF file names will have one of the following extensions ".dbf", ".tmp", ".log" or ".ctl"; and
  • OMF file names will have a "_" character immediately preceding the extension.

File name formats for different types of files:

  • Format for Data Files: o1_mf_%t_%u_.dbf (Example: o1_mf_system_wo94n2xi_.dbf)
  • Format for Temp Files: o1_mf_%t_%u_.tmp (Example: o1_mf_tempts_4n2xwo9i_.tmp)
  • Format for Control Files: o1_mf_%u_.ctl (Example: o1_mf_94n2xiwo_.ctl)
  • Format for Log Files: o1_mf_%g_%u_.log (Example: o1_mf_1_dygh80r6_.log)


  • %u = An 8-character string used to guarantee uniqueness
  • %g = Online Redo Log File Group number
  • %t = Tablespace Name

To check what directories and file names are used by a given database, execute the following SQL statements:

SQL> select * from sys.v_$controlfile;
SQL> select * from sys.v_$logfile;
SQL> select * from sys.v_$datafile;
SQL> select * from sys.v_$tempfile;

Managing Data and Temp Files using OMF:

Data and TEMP files will be created in the directory specified by the DB_CREATE_FILE_DEST parameter. Data files will have a *.dbf extension and TEMP files a *.tmp extension. Some examples:

-- Create a new tablespace with 1 file, 100M in size and autoextend on
SQL> create tablespace ts1;

-- Specify non-default file size
SQL> create tablespace ts2 datafile size 400m autoextend off; 

-- Limit file size to 800M
SQL> create tablespace ts3 datafile autoextend on maxsize 800m;

 -- Create tablespace with 3 files - 60M
SQL> create tablespace ts4 datafile size 20m, size 20m, size 20m;

-- Create TEMPORARY tablespace with 1 x TEMP
SQL> create temporary tablespace temp_ts; file

-- Add second TEMP file to tablespace
SQL> alter tablespace temp_ts add tempfile;

-- Create UNDO tablespace
SQL> create undo tablespace undo_ts;

Look at this CREATE DATABASE example:

sqlplus /nolog
SQL> startup nomount pfile=init.ora;
SQL> create database ORCL
    datafile size 400m         -- size of datafile for the SYSTEM tablespace
    default temporary tablespace temp_ts tempfile size 10m
    undo tablespace undo_ts datafile size 10m;

Managing Redo Log Files using OMF:

One can specify up to 5 directories for multiplexed redo log files. See parameter DB_CREATE_ONLINE_LOG_DEST_n. Example:

SQL> alter database add logfile;

Managing Control Files using OMF:

Oracle will create a control file in each or the log file directories specified (DB_CREATE_ONLINE_LOG_DEST_n). If none was specified, Oracle will create a single control file in the data file destination (DB_CREATE_FILE_DEST).

When using SPFILEs, Oracle will even set the CONTROL_FILES= parameter. One needs to set this parameter manually when using a normal INIT.ORA parameter file.

Removing files:

When removing a tablespace or log file, Oracle will automatically delete the associated OMF operating system files. All non-OMF files will remain on disk and must be manually deleted by the DBA. For example: these operations will delete the related objects, with their OMF datafiles from the operating system:

SQL> drop tablespace tbs_1;

SQL> alter database tempfile temp_ts drop;

SQL> alter database drop logfile member '/oradata/o1_mf_3_wo94n2xi_.log';
SQL> alter database drop logfile  group 3;

Note, one can also request Oracle to delete non-OMF files when specifying the "including contents and datafiles" option. Look at this example:

SQL> drop tablespace NON_OMS_TS including contents and datafiles;

Convert OMF-files to NON-OMF Files and vice versa:

One can switch data files between OMF and non-OMF mode by renaming them. Look at this example converting a non-OMF file to OMF:

define f=/oradata/x.dbf
define omf_f=/oradata/o1_mf_test_00000000_.dbf

-- Create a new tablespace using non-OMF files...
create tablespace test datafile '&&f' SIZE 1M;

-- Rename non-OMF file to OMF standard name...
alter database datafile '&&f' offline;
! mv &&f &&omf_f
alter database rename file '&&f' to '&&omf_f';
recover datafile '&&omf_f';
alter database datafile '&&omf_f' online;

-- Drop tablespace - Oracle will delete the tablespace's files!!!
drop tablespace test;

OMF Demonstration 1: Create a tablespace and add files to it

This example will create a new tablespace called TEST, and add a second datafile in another directory:

alter system set db_create_file_dest='/oradata1;
create tablespace test;
alter system set db_create_file_dest='/oradata2;
alter tablespace test add datafile;

OMF Demonstration 2: Create a new database

This example will create a tiny Oracle database for testing purposes. Before starting set the ORACLE_SID environment varable to the database name ("test" in the example below). After that, create an INIT.ORA file with single entry: "DB_NAME=test".

connect / as sysdba

-- Startup instance
startup nomount pfile=./init.ora

-- Create SPFILE from the INIT.ORA file...
create spfile from pfile='./init.ora';

-- Restart the Database with new SPFILE...
startup nomount force

-- Set DB location in SPFILE and MEMORY...
alter system set DB_CREATE_FILE_DEST="/oradata";

-- Create new database, SPFILE will be updated with CONTROL_FILES...
create database;


1. Oracle9i Release 2 (9.2) Database Administrator's Guide

Chapter 3: Using Oracle-Managed Files