A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Oracle Managed Files 11g & 12c

1 min read
pile of folders

Photo by Pixabay on Pexels.com

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