Re: Problem renaming a Tablespace Datafile in 10.2.0.4

From: ddf <oratune_at_msn.com>
Date: Mon, 11 Jan 2010 06:51:18 -0800 (PST)
Message-ID: <de73796b-dcc5-47ea-b457-f4c2086b6f7c_at_a32g2000yqm.googlegroups.com>



On Jan 11, 9:40 am, Johne_uk <edg..._at_tiscali.co.uk> wrote:
> Hi
>
> I have a tablespace called EXT_DATA which has 4 datafiles. When I
> created the 4th datafile I accidentally prefixed it with a period and
> want to remove this as it is causing issues with my backup scripts. I
> brought the tablespace offline and issued the following command.
>
> alter tablespace ext_data rename datafile '/data1/oradata/
> oranc1/.ext_data_4.dbf' to '/data1/oradata/oranc1/ext_data_4.dbf';
>
> However, it does not work as the period causes issues.
>
> alter tablespace ext_data rename datafile '/data1/oradata/
> oranc1/.ext_data_4.dbf' to '/data1/oradata/oranc1/ext_data_4.dbf'
> *
> ERROR at line 1:
> ORA-01525: error in renaming data files
> ORA-01141: error renaming data file 15 - new file
> '/data1/oradata/oranc1/ext_data_4.dbf' not found
> ORA-01110: data file 15: '/data1/oradata/oranc1/.ext_data_4.dbf'
> ORA-27037: unable to obtain file status
> SVR4 Error: 2: No such file or directory
> Additional information: 3
>
> I'm sure there is a simple answer to renmaing this datafile but I
> simply can't find it. Any offers off assistance would be greatly
> appreciated.
>
> Thanks in advance

That simple answer is:

  1. Copy or rename the datafile at the O/S level BEFORE attempting to rename it in the database. Take the tablespace offline, copy the datafile to the proper name, rename the datafile in the database, recover the tablespace and then bring the tablespace online. You should then be able to drop the old datafile at the O/S level. 'Renaming' the datafile in the database merely updates the data dictionary, it does NOT rename files at the O/S level.

David Fitzjarrell Received on Mon Jan 11 2010 - 08:51:18 CST

Original text of this message