Moving Datafiles from drive to another

[savourys]

Is it possible to move datafiles from one drive to the other? I have got to do a year end on our db server but their is not enough space on one drive to do this. So i need to know if it poss to do this and then Enterprise manager console tell the system where they are.

HELP!

[davey23uk]

yes you can

take the tablespace offline (or startup mount)

alter database datafile 'xxxx' rename to xxx' then copy the file to wherever you want it to be then online your tablespace or open it, make sure you can use the same tablespace and remove old file.

The sql syntax may be wrong - it is from memory

[marist89]

ALTER DATABASE RENAME FILE '/u01/foo.bar' TO '/u02/foo.bar'

[rajabalachandra]

Originally posted by davey23uk
yes you can

take the tablespace offline (or startup mount)

alter database datafile 'xxxx' rename to xxx' then copy the file to wherever you want it to be then online your tablespace or open it, make sure you can use the same tablespace and remove old file.

The sql syntax may be wrong - it is from memory

I think the order is very important.


1. take the tablespace offline.

2. copy the file to new location

3. alter database rename file oldname to newname

4. take the tablespace online.

because, when you rename the file it looks for file in the
new location.

-Raja

[savourys]

Take it nice and slowy, I am a newbie to Oracle!! Not actually done the course yet but the manager has left and I have got the job of looking after the DB. Could you explain to me a bit more clearly?

I assume when you take the DB offline you use Enterprise manager console yeah?

i understand that you have got to take it offline before you move it! But how do you tell it where the "new" files are?

[rajabalachandra]

launch sqlplus

connect as sys

the following are the commands to enter.


1. alter tablespace users offline;

2. from your operating system prompt copy files from
current location to new location.
(eg. in windows we copy from d drive to e drive
copy d:\oracle\oradata\test\users01.dbf e:\oracle\oradata\test
)

3. alter database rename file 'd:\oracle\oradata\test\users01.dbf' to 'e:\oracle\oradata\test\users01.dbf' ;

4. alter tablespace users online;


and very important step is read oracle documentation,
if you want to continue as oracle DBA.

-Raja

[DaPi]

Take a backup first

savourys, my advice in your situation would be to phone Oracle for a half-day intervention. It's not difficult and it would only take half an hour - but it's your management should be carrying the can, not you.

[savourys]

Cheers muchly for everybody's help! I have managed to move the files
now using enterprise manager console cos I couldnt get the sql to work but once I used the console I selected show sql I now know what I did wrong.

It got abit nerve racking at one point because oracel said that the file was knacked! But I had made a copy of it!

but all in all it is still working. Not bad for a beginner if I do say so myself!


Cheers again
Sean