rename datafile file not found error

[Former Member]

hi experts,

I extended our tablespace by adding some datafiles.

1. Previously, my datafile is upto sr3_29 with filename SR3.DATA29.

When I added another datafile, I created directory sr3_30 and mistakenly named the datafile as SR3.DATA25 instead of SR3.DATA30. I already have that filename on my sr3_25 directory.

Am I going to have problems once the system uses the newly created SR3.DATA25? will it matter if there's a duplicate datafile on separate directory?

2. I tried to rename the datafile using the sql script alter tablespace <tablespace name> rename datafile 'old file' to 'new file';. It prompted me with error ORA-01141 - new file not found. It seems that the system was expecting an existing datafile.

Do I have to create a datafile with the correct filename on my directory sr3_30? then do the renaming?

Please suggest!

Thank you in advance!

[Former Member · Accepted solution]

For point number 1. You don't have   duplicate fille in your database just because the filenames are same. Filename alone is not important but absolute path is. Had it been duplicate thing you would never been able to create it !! You think an RDBMS having such a robust mechanism for duplicate records in a table would allow such things.Leave RDBMS aside can you a duplicate file at OS level having same name and absolute path ?? No you can't.Further data file will have a file_id assigned. For each data file a different id is assigned. So that helps.But what helps more is file_name isabsolute path and is saved as such in DBA_DATA_FILES table.

For point # 2 before you do this excercise you should shutdown you DB cleanly and then physically move the file at OS level. Then start up mount and issue move  command or alter tablespace rename command. DB can't move the file for you.

Regards.

Ruchit        

[Former Member · Accepted solution]

Hi Ruchit,

Sorry for the late reply. Thank you for the clarification. I'll get back to you on this one. I'll just try the suggestion on #2.

Thank you.