Donald K. Burleson Oracle Tips

Oracle9i Managed Files (OMF)

One exciting new feature of Oracle9i is the automatic management of Oracle files at the OS level.  Prior to Oracle9i, the DBA had to manually removed data files for dropped tablespaces and carefully check file locations before creating new tablespaces.

To see how OMF, works, we start be defining a new parameter called db_create_file_dest.

alter system set db_create_file_dest=’/u01/oradata/PROD’;

Once the db_create_file_dest parameter is set you will be able to create tablespaces without specifying the file name:

create tablespace

   omf1

datafile size 100m

locally managed;

Once created, Oracle handles the allocation and naming of the files.  Oracle OMF uses the following naming convention for files with 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,

       %t is the tablespace name:

       Controlfiles - ora_%u.ctl

       Redo Log Files - ora_%g_%u.log

       Datafiles - ora_%t_%u.dbf

       Temporary Datafiles - ora_%t_%u.tmp

The Oracle9i OMF facility also has new alert log messages to tell us when OMF datafiles have changed.  Here is s ample alert log:

Fri Mar 23 01:44:46 2001

CREATE TABLESPACE omf1 DATAFILE SIZE 10M AUTOEXTEND OFF

Fri Mar 23 01:44:47 2001

Created Oracle managed file /databases/ed25/OMF1/ora_omf1_xcp6rg84.dbf

Completed: CREATE TABLESPACE omf1 DATAFILE SIZE 10M AUTOEXTE

Fri Mar 23 01:45:45 2001

drop tablespace omf1

Fri Mar 23 01:45:45 2001

Deleted Oracle managed file /databases/ed25/OMF1/ora_omf_xcp6rg84.dbf

Completed: drop tablespace omf1

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm