Oracle Managed Files (OMF) lets the database pick file names, sizes, and locations for many database files. When OMF is enabled and correctly configured, you can create a tablespace without specifying a DATAFILE or TEMPFILE clause. If OMF isn’t configured, running CREATE TABLESPACE without a file clause raises ORA-02199: missing DATAFILE/TEMPFILE clause.

OMF can manage:

• Data files and temp files
• Online redo log files
• Control files

To use OMF, configure the relevant initialization parameters. For data and temp files, the primary setting is DB_CREATE_FILE_DEST.

What Triggers ORA-02199

If you run CREATE TABLESPACE without a DATAFILE/TEMPFILE clause and DB_CREATE_FILE_DEST is not set, Oracle does not know where to place the file and returns ORA-02199.

Example:

SQL> CREATE TABLESPACE ts_omf;  -- fails without OMF path

CREATE TABLESPACE ts_omf
*
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

Configure OMF for Data and Temp Files

1. Check whether DB_CREATE_FILE_DEST is set:

SQL> SHOW PARAMETER db_create_file_dest

NAME                      TYPE    VALUE
------------------------- ------- ------------------------------
db_create_file_dest       string

2. Set a default location for OMF-managed files:

SQL> ALTER SYSTEM SET db_create_file_dest = '/u01/app/oradata/ORCL';

System altered.

• With this parameter in place, Oracle auto-generates names under the specified directory for data and temp files when they are created without explicit file clauses.
• Separate parameters exist for online logs (DB_CREATE_ONLINE_LOG_DEST_n) and other components when you want OMF to manage them as well.

Create Tablespaces with OMF

After DB_CREATE_FILE_DEST is configured, you can omit the DATAFILE clause:

SQL> CREATE TABLESPACE ts_omf; 

Tablespace created.

Inspect the data files recorded by the control file:

SQL> SELECT name FROM v$datafile ORDER BY name;

Look for an OMF-style path and filename:

• Example path: /u01/app/oradata/ORCL/ORCL/datafile/o1_mf_ts_omf_XXXXXXXX_.dbf

Check the file on the host:

SQL> HOST ls -lh /u01/app/oradata/ORCL/ORCL/datafile/o1_mf_ts_omf_XXXXXXXX_.dbf

Create another tablespace with a explicit size while still letting OMF name/place it:

SQL> CREATE TABLESPACE ts_sized DATAFILE SIZE 12M;

Tablespace created.

Dropping an OMF-created tablespace removes the OS file automatically:

SQL> DROP TABLESPACE ts_sized;

Tablespace dropped.

SQL> HOST ls -lh /u01/app/oradata/ORCL/ORCL/datafile/o1_mf_ts_sized_YYYYYYYY_.dbf
ls: cannot access ...: No such file or directory

Create and Drop Tablespaces Without OMF (Manual File Path)

If you specify a file name/path explicitly, Oracle treats it as a non-OMF data file. Dropping the tablespace without the INCLUDING CONTENTS AND DATAFILES clause removes dictionary metadata but leaves the physical file behind.

Create a tablespace with a manual file name:

SQL> CREATE TABLESPACE ts_manual
  2  DATAFILE '/u01/app/oradata/ORCL/ts_manual01.dbf' SIZE 10M;

Tablespace created.

Drop it without removing the OS file:

SQL> DROP TABLESPACE ts_manual;

Tablespace dropped.

SQL> HOST ls -lh /u01/app/oradata/ORCL/ts_manual01.dbf
-rw------- 1 oracle oinstall 11M Apr  7 17:01 /u01/app/oradata/ORCL/ts_manual01.dbf

To remove the physical file in one step, enclude INCLUDING CONNTENTS AND DATAFILES during the drop:

SQL> CREATE TABLESPACE ts_manual
  2  DATAFILE '/u01/app/oradata/ORCL/ts_manual01.dbf' REUSE;

Tablespace created.

SQL> DROP TABLESPACE ts_manual INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> HOST ls -lh /u01/app/oradata/ORCL/ts_manual01.dbf
ls: cannot access '/u01/app/oradata/ORCL/ts_manual01.dbf': No such file or directory

Quick Reference

• Error cause: ORA-02199 occurs when OMF isn’t configured and CREATE TABLESPACE omits DATAFILE/TEMPFILE
• Enable OMF default location: ALTER SYSTEM SET db_create_file_dest = ''
• Create OMF tablespace: CREATE TABLESPACE ;
• Create with OMF and size: CREATE TABLESPACE DATAFILE SIZE M;
• Create manual file: CREATE TABLESPACE DATAFILE '.dbf' SIZE M;
• Drop OMF tablespace (removes OS file): DROP TABLESPACE ;
• Drop manual-file tablespace and remove OS file: DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES;