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
SELECT file_name FROM dba_temp_files
SELECT member as file_name FROM v$logfile
SELECT name as file_name FROM v$controlfile)
GROUP BY file_name

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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.