move datafile with alter database or alter tablespace?

A datafile can be moved by either of the following procedures...

do an OS copy of the datafile to new location
shutdown the database & startup mount but not open
alter database rename file "oldname' 'newname'
alter database open
backup the controlfile
OS delete the original file


OS copy the file to new location
alter tablespace "name" offline
alter tablespace rename datafile 'oldname' 'newname'
alter tablespace 'name' online
backup the controlfile
OS delete the original file

both proc's achieve the same result - which is preferable? With the alter tablespace proc the process happens without an outage.

Like to hear some opinions on this....

I don't think there will be any problem with second option if the database verion 8x/81x and you are sure that you don't have any transactions currently running on that tablespace.

Except that, you offline the tablespace first and then OS copy the datafile.

Second one will be good choice
First make that datafile offline normal & then take backup.
and then use alter tablespace command..

Quote:

---

Originally posted by sreddy
I don't think there will be any problem with second option if the database verion 8x/81x and you are sure that you don't have any transactions currently running on that tablespace.

Except that, you offline the tablespace first and then OS copy the datafile.

---

Why 8.x? - the DB I want to move a file on is 7.2.3 running on AIX

how do you check for transactions running on the tablespace, should that matter? would the transactions just sit in the redo until the tablespace is back online, much like and online backup?

The second option is more commonly used, unless if you want to rename/move a dbf belonging to the system ts, in which case, you would have to shutdown the db and then rename at mount. Ongoing transactions will not be affected. They will get logged in the redo log files.

The reason I mentioned for only 8/8i is in vesrion 7 you need to have datafile timestamp match for database to recognize that datafile. So, you have to preserve the timestamp while OS copying the datafile with "cp -p" option.

It depends upon the volume of activity on that tablespace. It should work fine. I have just mentioned.

Hope this clears your Q...

[Edited by sreddy on 02-27-2001 at 01:36 PM]