Location: PL/SQL

Discussion: Duplicate names of datafilesReported This is a featured thread

Category: Discussion Forum / General Discussion
Showing 7 posts

smit_sign
Duplicate names of datafiles
Feb 14 2009, 9:47 AM EST | Post edited: Feb 14 2009, 9:47 AM EST
Can some one please provide the script to check the duplicate datafiles in database.

Thanks
Smit Do you find this valuable?    
luciofer
luciofer
1. RE: Duplicate names of datafiles
Feb 16 2009, 3:41 PM EST | Post edited: Feb 16 2009, 3:41 PM EST
And what's supposed to be that? Can you be more specific?

Cheers Do you find this valuable?    

smit_sign
2. RE: Duplicate names of datafiles
Feb 17 2009, 5:01 AM EST | Post edited: Feb 17 2009, 5:01 AM EST
My database has the datafile names similar like
/u08/oradata/users2_01.dbf
/u17/oradata/users2_01.dbf

I need a script which will give me the clear idea of directory stucture and the file name. So that it will be eady and precautionary incase I'm planning to move that to new disk.

Regards,
Smita Do you find this valuable?    
luciofer
luciofer
3. RE: Duplicate names of datafiles
Feb 17 2009, 7:32 AM EST | Post edited: Feb 17 2009, 7:32 AM EST
Connect as sysdba

select file_name from dba_data_files;

Cheers Do you find this valuable?    

smit_sign
4. RE: Duplicate names of datafiles
Feb 18 2009, 4:08 AM EST | Post edited: Feb 18 2009, 4:08 AM EST
Luciofer,

I guess you are still not clear what I want.

A query which tells me the duplicate file names only. select file_name from dba_data_files will give me all the file_name present in database.

If you observe closely given data. "users2_01.dbf " is present under 2 different directory stuctures means that filename is duplicated.


select replace(substr(file_name,instr(file_name,'/',-1)),'/',' ') as FileName from dba_data_files group by substr(file_name,instr(file_name,'/',-1)) having count(*)>1;

FILENAME
--------------------------------------------------------------------------------
data_large11.dbf
users2_01.dbf

Above qery indeed gives me name.. but I want the output in the following format which I'm lacking to retrieve.

DirectoryName FileName
------------------------ ----------------------
/u06/oradata/ /data_large01.dbf
/u07/oradata/ /data_large01.dbf
/u05/oradata/ /data_large01.dbf

Hope this is more clear to you now.

Thanks. Do you find this valuable?    
luciofer
luciofer
5. RE: Duplicate names of datafiles
Feb 18 2009, 9:08 AM EST | Post edited: Feb 18 2009, 9:08 AM EST
"Luciofer,

I guess you are still not clear what I want.

A query which tells me the duplicate file names only. select file_name from dba_data_files will give me all the file_name present in database.

If you observe closely given data. "users2_01.dbf " is present under 2 different directory stuctures means that filename is duplicated.


select replace(substr(file_name,instr(file_name,'/',-1)),'/',' ') as FileName from dba_data_files group by substr(file_name,instr(file_name,'/',-1)) having count(*)>1;

FILENAME
--------------------------------------------------------------------------------
data_large11.dbf
users2_01.dbf

Above qery indeed gives me name.. but I want the output in the following format which I'm lacking to retrieve.

DirectoryName FileName
------------------------ ----------------------
/u06/oradata/ /data_large01.dbf
/u07/oradata/ /data_large01.dbf
/u05/oradata/ /data_large01.dbf

Hope this is more clear to you now.

Thanks."
I got that.

select substr(file_name,instr(file_name,'/',-1)+1) from dba_data_files group by substr(file_name,instr(file_name,'/',-1)+1) having count(*) > 1

Cheers Do you find this valuable?    

samrat.wani
6. RE: Duplicate names of datafiles
Jun 29 2009, 5:51 AM EDT | Post edited: Jun 29 2009, 5:51 AM EDT
Here is your answer:


set lines 130
set pages 2000
col file_name for a50
break on tablespace_name

select tablespace_name, file_name
from dba_data_files ddf ,
(
select substr(file_name,16) sstr
from dba_data_files
group by substr(file_name,16)
having count(substr( file_name ,16))> 1
) dup
where instr(ddf.file_name, dup.sstr) > 0; Do you find this valuable?