How To Find Datafiles With The Same Name

Sometimes you want to check for datafiles with the same name in your database when you have a major storage migration project or for any other reason.

Here is a little script to check if the database has datafiles with the same name in different directories.

1- To find the duplicated datafiles in the database:

SET LINE 140
SET PAGESIZE 2000
SET HEADING OFF

SELECT COUNT (*), SUBSTR (file_name, INSTR (file_name, '/', -1))
FROM dba_data_files
GROUP BY SUBSTR (file_name, INSTR (file_name, '/', -1))
HAVING COUNT (*) > 1;

2- To find the pathes for the duplicated datafiles:

SELECT file_name full_file_name,
SUBSTR (file_name, INSTR (file_name, '/', -1)) datafile_name
FROM dba_data_files
WHERE (SUBSTR (file_name, INSTR (file_name, '/', -1))) IN
( SELECT SUBSTR (file_name, INSTR (file_name, '/', -1))
FROM dba_data_files
GROUP BY SUBSTR (file_name, INSTR (file_name, '/', -1))
HAVING COUNT (*) > 1);