Find duplicate file names

You can find duplicate file names, on different file systems, by using this query:

set lines 200
col "file_name" format a30
col "tablespace" format a30

set trimspool on
spool duplicates.lst
alter session set nls_language='american';

select t.name "tablespace",
trim(
            substr(f.name,
                (instr(f.name,'/', -1, 1) +1)
                )
               ) "file_name", count(*)
from v$datafile f join v$tablespace t
on (f.ts# = t.ts#)
group by t.name,
         trim(
            substr(f.name,
                (instr(f.name,'/', -1, 1) +1)
                )
               )
having count(*) > 1;



exit