Simplified database file creation using OMF

OMF (Oracle Managed Files) in oracle is used to simplify creation of database files as Oracle does all OS operations and file naming. It helps:

·         Automatic cleanup of the filesystem when database objects are dropped.

·         Standardized naming of database files.

·         Increased portability since file specifications are not needed.

·         Simplified creation of test systems on differing operating systems.

·         No unused files wasting disk space.

The location of database files is defined using the DB_CREATE_FILE_DEST parameter

ALTER SYSTEM SET DB_CREATE_FILE_DEST=’D:\oracle\oradata\testdata\OMF\’;

Files typically have a default size of 100M and are named using the following formats where u% is a unique 8 digit code, g% is the logfile group number, 

and %t is the tablespace name:

File Type	Format
Controlfiles	ora_%u.ctl
Redo Log Files	ora_%g_%u.log
Datafiles	ora_%t_%u.dbf
Temporary Datafiles	ora_%t_%u.tmp

Example:

Create tablespace test_omf;

The resultant datafiles will have a default size of 100M and AUTOEXTEND 

UNLIMITED.

CREATE TABLESPACE test_omf DATAFILE SIZE 150M;

ALTER TABLESPACE test_omf ADD DATAFILE;

DROP TABLESPACE test_omf

:-)