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:

2 Responses to “How to Move a Datafile to Different Location”

  1. Dilawar says:

    Good & Easy step

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

    Please reply me on my mail also

    Thanks

    • Gokhan Atil says:

      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;

Leave a Reply

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