Home » RDBMS Server » Server Administration » duplicate datafile name (Oracle 10g on Solaris 10)
duplicate datafile name [message #545437] Wed, 29 February 2012 08:16 Go to next message
caprikar
Messages: 222
Registered: March 2007
Senior Member
Hi,
I have oracle 10g running on Solaris with file system and some one created database files with same name but in different directories for example data01.dbf in two different directories, say /u01/oradata/data01.dbf and /u02/oradata/data01.dbf. Now, I want to find out the duplicate datafiles (data01.dbf in this case) sitting in different directories, is there anyway to find this out?
Thanks.
Re: duplicate datafile name [message #545438 is a reply to message #545437] Wed, 29 February 2012 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
query dba_data_files
Re: duplicate datafile name [message #545439 is a reply to message #545438] Wed, 29 February 2012 08:33 Go to previous messageGo to next message
caprikar
Messages: 222
Registered: March 2007
Senior Member
I want to get only the duplicate files and not all the files.
Thanks
Re: duplicate datafile name [message #545440 is a reply to message #545439] Wed, 29 February 2012 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what is your problem in writing the query?
Clues: use SUBSTR et INSTR, or REGEXP_SUBSTR.

Regards
Michel
Re: duplicate datafile name [message #545441 is a reply to message #545439] Wed, 29 February 2012 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
https://forums.oracle.com/forums/thread.jspa?messageID=10180050#10180050
Re: duplicate datafile name [message #545442 is a reply to message #545439] Wed, 29 February 2012 08:43 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well unless you've got a ridiculously large number of datafiles you should be able to spot the duplicates by eye, but if you want to use sql then just use a combo of instr and substr to get the characters in the file_name column that come after the final /
Re: duplicate datafile name [message #545451 is a reply to message #545440] Wed, 29 February 2012 09:19 Go to previous messageGo to next message
caprikar
Messages: 222
Registered: March 2007
Senior Member
"use SUBSTR et INSTR, or REGEXP_SUBSTR", this is what I was looking for.
Thanks
Re: duplicate datafile name [message #545452 is a reply to message #545442] Wed, 29 February 2012 09:20 Go to previous messageGo to next message
caprikar
Messages: 222
Registered: March 2007
Senior Member
Yes, I do have close to 500 files, that's why I had to write a script to figure this out.
Re: duplicate datafile name [message #545454 is a reply to message #545452] Wed, 29 February 2012 09:27 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
500? for one DB? ouch.
Re: duplicate datafile name [message #545455 is a reply to message #545454] Wed, 29 February 2012 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
>Yes, I do have close to 500 files, that's why I had to write a script to figure this out.
I am curious as to what problem, this will help solve.
Re: duplicate datafile name [message #545462 is a reply to message #545455] Wed, 29 February 2012 09:51 Go to previous messageGo to next message
caprikar
Messages: 222
Registered: March 2007
Senior Member
The reason is, I have to backup this database and restore it in a different server under only one file system.
Thanks
Re: duplicate datafile name [message #545693 is a reply to message #545462] Thu, 01 March 2012 13:09 Go to previous message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
caprikar wrote on Wed, 29 February 2012 07:51
The reason is, I have to backup this database and restore it in a different server under only one file system.
Thanks

> /u01/oradata/data01.dbf and /u02/oradata/data01.dbf
NOTHING prevents you from supporting the two paths above on a single filesystem.
Previous Topic: Flashback Q regarding statistics (merged 2)
Next Topic: Exit of Oracle Universal Installer while clicking next button (3 Merged)
Goto Forum:
  


Current Time: Tue Sep 30 20:01:25 CDT 2014

Total time taken to generate the page: 0.09859 seconds