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: