How to find datafiles with same name?

Sometimes you want to check for datafiles with the same name in the database, maybe you are new to the env and 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.

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
/

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