Script to find duplicate datafile names

  • Published on Nov 23 2012
  • # 722
  • oracle

Whenever you want to duplicate (copy/scp) a database and place all datafiles into one single directory you always have to check if there are no duplicate filenames.

Eg like;

/m001/oradata/oracle_sid/filename1.dbf
/m002/oradata/oracle_sid/filename1.dbf

scp /m*/oradata/oracle_sid/*dbf remote_host:/m001/oradata/dup_oracle_sid/.

SQL> select substr ( file_name, instr( file_name, '/', -1)) file_name, count(*)
  2  from dba_data_files
  3  group by substr ( file_name,instr( file_name, '/', -1)) 
  4  having count(*) > 1
  5  /

FILE_NAME                               COUNT(*)
------------------------------------- ----------
/****p1i005ds10.dbf                            2
/****p1i005ds11.dbf                            2

SQL> 

And to get the full paths – rows from dba_data_files:

SQL> select *
  2  from dba_data_files
  3  where substr ( file_name, instr( file_name, '/', -1)) in (
  4     select file_name from (
  5        select substr ( file_name, instr( file_name, '/', -1)) file_name, 
  6        count(*)
  7        from dba_data_files
  8        group by substr ( file_name,instr( file_name, '/', -1))
  9        having count(*) > 1 )
 10     );

FILE_NAME                                                               FILE_ID
---------------------------------------------------------------------- --------
/m021/oradata/********/********p1boff00106.dbf                              153
/m001/oradata/********/********p1boff00106.dbf                              154
/m005/oradata/********/********p1boff00209.dbf                               40
/m002/oradata/********/********p1boff00209.dbf                               39
/m006/oradata/********/********p1boffis08.dbf                                52
/m004/oradata/********/********p1boffis08.dbf                               163

6 rows selected.


Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>