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 this post Share on Facebook0Share on Google+0Share on LinkedIn0Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), 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.

2 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;

Leave Comment

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