Oracle Managed Files 11g & 12c

by

11g

When OMF is enabled and If a datafile location is incorrect the file gets created in dbs location

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +SHARK
SQL>

SQL> CREATE TABLESPACE TEST DATAFILE 'SHARK' SIZE 100M;

Tablespace created.


SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

FILE_NAME          TABLESPACE_NAME
-----------------  ------------------------------
TEST               /u01/oracle/product/11.2.0.4/db/dbs/shark

12c

Oracle Managed Files will ignore the user provided datafile input and creates datafiles in OMF managed  location

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +SHARK


SQL> CREATE TABLESPACE TEST DATAFILE 'SHARK' SIZE 2M ;

Tablespace created.

SQL> select FILE_NAME, TABLESPACE_NAME from cdb_data_files where TABLESPACE_NAME='TEST';

FILE_NAME              TABLESPACE_NAME
------------------     ------------------------------
TEST                   +SHARK/CSHARK/ED7B4B/shark