move datafile with alter database or alter tablespace?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: move datafile with alter database or alter tablespace?

  1. #1
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82
    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....

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  3. #3
    Join Date
    Feb 2001
    Location
    Baltimore,MD USA
    Posts
    27
    Second one will be good choice
    First make that datafile offline normal & then take backup.
    and then use alter tablespace command..


  4. #4
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82
    [QUOTE][i]Originally posted by sreddy [/i]
    [B]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. [/B][/QUOTE]

    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?

  5. #5
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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]
    Reddy,Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width