OraFAQ Forum: Server Administration » alter database rename file

Home » RDBMS Server » Server Administration » alter database rename file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

alter database rename file [message #152339]

Thu, 22 December 2005 10:45

SJDewar
Messages: 15
Registered: July 2005

Junior Member

Hi,

I need to move datafiles and I'm planning on using the 'alter database rename file' option. In a Windows environment do you still use the single quotes around the paths to the files?

e.g. alter database rename file 'd:\old_dest' to 'e:\new_dest'

Thanks in advance,

S.

Report message to a moderator

Re: alter database rename file [message #152341 is a reply to message #152339]

Thu, 22 December 2005 10:55

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

You are doing this in test first before in production, right? What happened during your test?

Report message to a moderator

Re: alter database rename file [message #152342 is a reply to message #152339]

Thu, 22 December 2005 11:01

SJDewar
Messages: 15
Registered: July 2005

Junior Member

Hi,

I haven't tried it on any database yet, thought I'd get the info first. I've looked on various sites and I see that single quotes are used but the majority of the examples are for unix environments.

S.

Report message to a moderator

Re: alter database rename file [message #152352 is a reply to message #152339]

Thu, 22 December 2005 12:31

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

I think it is single quotes on windows as well:

create tablespace new_ts datafile 'mydf.dbf' size 1m;

Report message to a moderator

Re: alter database rename file [message #152356 is a reply to message #152339]

Thu, 22 December 2005 12:48

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Here is a little more of a complete example. Remember that you must physically rename the datafile on your OS yourself (between the steps of bringing tablespace offline and renaming the datafile in oracle). If you don't remember to rename the os file before the rename command (I had forgotten), you'll get something like:

MYDBA > alter database rename file 'mydf.dbf' to 'newdf.dbf';
alter database rename file 'mydf.dbf' to 'newdf.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6 - new file 'newdf.dbf' not found
ORA-01110: data file 6: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MYDF.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

So the steps would be...

MYDBA > create tablespace new_ts datafile 'mydf.dbf' size 1m;

Tablespace created.

MYDBA > alter tablespace new_ts offline;

Tablespace altered.

-- rename datafile on OS here

MYDBA > alter database rename file 'mydf.dbf' to 'newdf.dbf';

Database altered.

MYDBA > alter tablespace new_ts online;

Tablespace altered.

MYDBA >

Report message to a moderator

Re: alter database rename file [message #153784 is a reply to message #152339]

Wed, 04 January 2006 03:06

SJDewar
Messages: 15
Registered: July 2005

Junior Member

Hi,

Thanks for the replies, very helpful.

S.

Report message to a moderator

Previous Topic:	how to create multiple database name?
Next Topic:	How to create schema?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 04 01:37:19 CST 2025