Tales From A Lazy Fat DBA

What are Oracle-Managed Files?
Using Oracle-managed files simplifies the administration of an Oracle database. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames.

– Provides default location, name and size
– OMF is still optional. Normal file creation techniques still available

Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures:

Tablespaces
Online redo log files
Control files

First you have to enable OMF by altering parameter db_create_file_dest.
Example:
SQL> show parameter db_create

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string

Here i’m trying to create a tablespace with name ‘tb1’ but you’ll recieve error message asking you to provide DATAFILE/TEMPFILE clause.

SQL> create tablespace tbi;
create tablespace tbi
                    *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
Alter parameter db_create_file_dest.
SQL> alter system set db_create_file_dest=’/u01/app/oracle/oradata/’ scope=both;

System altered.
SQL> show parameter db_create_file_dest;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string      /u01/app/oracle/oradata/

Let’s try to create Tablespace in same fashion we tried earlier.
SQL> create tablespace tb1;

Tablespace created.
Done.