Top 60 Oracle Blogs

Recent comments


Is a datafile OMF or not?

TL;DR: there’s no flag (only the name tells it), but a function

You want to know which files are Oracle Managed Files or not? Maybe because you like OMF (like I do as I show no interest for file names when I have tablespaces) and you have non-OMF files that you want to move to OMF ones.

There’s no flag in V$DATABASE or DBA_DATA_FILES. Only the name of the file (starting with ‘o1_mf’, like Oracle Managed Files, and ending with ‘.dbf’, like Database File) makes it OMF or not. But I don’t like to rely on name convention so I searched how Oracle is doing it. There’s an isFileNameOMF procedure in the RMAN packages.


Here is a procedure that displays which files are ASM, and which ones are regular user-managed ones:

dbca now makes it easy to configure OMF on file systems

Up until – and including – Oracle 12.1 I always found it not-quite-so-straight-forward to create a new database using Database Creation Assistant (dbca) and configure it with Oracle Managed Files (OMF) on a file system in silent mode. I really like to use OMF in my lab databases as it saves me a lot of typing. I have also seen Oracle databases deployed in the cloud on file systems without ASM. So I was quite happy to see the syntax for dbca -silent -createDatabase was extended.

This post has been written using Oracle 18.4.0 on Linux.