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.

dbms_Backup_Restore.isFileNameOmf

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

declare
 isomf boolean;
 isasm boolean;
begin
 for c in (select name from v$datafile)
 loop
  dbms_backup_restore.isfilenameomf(c.name,isomf,isasm);
  if isasm then dbms_output.put('ASM '); else
   if isomf then dbms_output.put('OMF '); else dbms_output.put('reg '); end if;
  end if;
  dbms_output.put_line(c.name);
 end loop;
end;

Output on non-ASM with some OMF and some regular files

The procedure has another boolean which I guess is for controlfile autobackup names, but I don’t need it.

‘Rename’ OMF file names

In 12c, the procedure can be called from an in-line function in a SQL query. Here is how I generate all the ‘ALTER DATABASE MOVE DATAFILE’ commands for non-OMF files:

with function isomf( name v$datafile.name%type) return char as
 isomf boolean;
 isasm boolean;
begin
 dbms_backup_restore.isfilenameomf(name,isomf,isasm);
 if isomf then return 'Y'; else return 'N'; end if;
end;
select 'alter database move datafile '''||file_name||''';'
from dba_data_files
where isomf(file_name)='N';
/

Then, the files will go to the db_create_file_dest with an OMF name that you can change at session level.

Side comment about online “rename”

The previous script uses the 12c Online datafile move to rename them. But I’ve put “rename” in quotes because it copies the file even when it remains within the same filesystem. That’s different from the Linux “mv” which changes only the name when in the same filesystem. Here is an example:

host pkill -9 strace
connect / as sysdba
create tablespace franck datafile size 5M;
column spid new_value pid
select spid from v$process join v$session on v$session.paddr=v$process.addr where sid=sys_context('userenv','sid');
column spid clear
column file_name new_value file_name
select file_name from dba_data_files order by bytes fetch first 1 rows only;
column file_name clear
set escape on
host strace -fy -e trace=file,desc -p &pid 2>\&1 | grep '&file_name'\& :
alter database move datafile '&file_name';
disconnect
host grep dbf strace.log
drop tablespace franck including datafiles;

Update 02-AUG-2019

Thanks to @Mikhail Velikikh here is a function that is probably a better solution: DBMS_METADATA_UTIL.IS_OMF

dbmsmetu.sql

It actually calls the UTL_XML.ISNAMEOMF but has the advantage to be a function, returning 1 for OMF file names or 0 otherwise.