How to find files with duplicate names in filesystem?

If you are doing migrating to a new filesystem and moving files, it may be a good idea to check before migrating the filenames are unique across all filesystems by running the following query you find the files that have the same name.

If the query returns no rows found then you don’t have duplicate filenames.

SELECT SUBSTR(file_name, instr(file_name, ‘/’, -1))
FROM (SELECT file_name FROM dba_data_files
UNION ALL
SELECT file_name FROM dba_temp_files
UNION ALL
SELECT member as file_name FROM v$logfile
UNION ALL
SELECT name as file_name FROM v$controlfile)
GROUP BY file_name
HAVING COUNT(1) > 1;

Note: On windows the filenames are not case-sensitive so adding upper/lower to convert to same case would be required to ensure filenames are unique.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s