Moving datafiles

[THoey]

Can anyone point me in the direction of the steps to move a datafile from one drive to another? I am sure that this has been discussed before, but I can't get the search to work...

I'd appreciate it greatly... Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[MikeLacey]

Hi Terry,

I've done this once or twice and it's quite easy as I remember. Look in the documentation for the COPY command. Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[THoey]

I found a better way to do it:

1. Go into SQL DBA Studio and take the tablespace offline.

2. Copy the datafile to the new directory using MS Explorer or a DOS shell.

3. Using SQL DBA Studio, change the path of the datafile.

4. Using SQL DBA Studio, put the tablespace back online.

Make sure to save any changes to your instance files.

Anyone know any problems of this please let me know before I go too far... Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[ljspop]

Terry,

What is SQL DBA Studio?

FWIW, the SQL/OS Commands are similar to what you described:

1) Take the tablespace offline
2) Use OS commands to move or (preferably) copy the datafile
3) ALTER TABLESPACE tablespace RENAME DATAFILE 'filename'
TO 'filename';
4) Bring the tablespace online
5) Delete the old file after checking the new one Bob Lowell
ljspop@yahoo.com

 

[THoey]

SQL DBA Studio is a GUI tool that comes with Oracle 8.1.6, not sure if it came out with earlier versions of 8. It kinda combines all the tools that came with Oracle 7's Enterprise Manager (Instance, Storage, Security, Schema).

I have it set up so that I can control six different databases, some of them version 8, others version 7.3.4. I am still learning about it, we got the install CD, but no documentation. So far, it works pretty good. Better than 7's having to log into each tool separately... Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[bookouri]

i tried to do the same thing with the gui tools and it would not allow me to take the datafiles off line. I kept getting some oracle error stating that "media recovery had to be activated" or something like that. I ended up having to use the command line to actually get the job done.

 

[pdalmia]

It is not be tough.Shutdown your database copy your datafile for source disk to target disk .
Mount your database use alter database rename datafile command
open the database using alter database open

Hope this will help you out
Pawan