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
  •  
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

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 a Reply to Quinc Cancel reply

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.