How to Move a Datafile to Different Location

If the database is in archivelog mode, you can move the datafile while database is up. Take the datafile offline, copy (or move) the datafile to the new location:

Then issue the following commands to rename the datafile and make it online:

If the database is in no archivelog mode, you need to shutdown the database, open the database in mount mode and copy/move the datafile:

Then rename the file and open the database:

Please share
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases, and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

3 Comments

  1. Dilawar

    Good & Easy step

    I am newbie. Is this step applicable to 11gR2 & 12c

    Please reply me on my mail also

    Thanks

    • Gokhan Atil

      Dilawar,

      In 12c, you can use “ALTER DATABASE MOVE DATAFILE ‘old_full_path’ TO ‘new_full_path'” to move datafiles online. It doesn’t need to recover the file after movement and there’s no need to move the file with OS copy commands;

  2. Quinc

    Is it possible to manually move these files to a directory with the same name and then remove the original directory? I am trying to move from a failing san to a windows share.

    Thanks!

Leave Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.