Thursday, June 19, 2014

Move That Datafile!

Moving datafiles has always been a pain.  There are several steps, it’s fairly easy to make a mistake and it requires the datafile to be offline.  There are also different steps depending on whether the database is in ARCHIVELOG mode or not.  In ARCHIVELOG mode, the steps are…

1)      Take the tablespace containing the datafile offline
2)      Copy/rename the datafile at the OS layer
3)      Use ALTER TABLESPACE…RENAME DATAFILE to rename the datafile so that the controlfile will be aware of it
4)      Backup the database for recovery purposes (recommended)

If the database is in NOARCHIVELOG mode, you have to shutdown the DB, put it in the MOUNT state, etc, etc.  That’s certainly not that hard to do, but you get the feeling that there should be a better way.  Now in Oracle 12c, there is – using the ALTER DATABASE MOVE DATAFILE command.  With this command, you can move a datafile, while it’s online, in one simple step.  Let’s set this up.

SQL> create tablespace test datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf' size 10m;

Tablespace created.

SQL> create table altdotoracle.tab1 (col1 number) tablespace test;

Table created.

SQL> insert into altdotoracle.tab1 values (1);

1 row created.

SQL> commit;

Commit complete.

Let’s go the extra mile and lock the table in that datafile in another session.

SQL> lock table altdotoracle.tab1 in exclusive mode;

Table(s) Locked.

Now let’s use the command.

SQL> alter database move datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf'
  2   to '/oracle/base/oradata/TEST1/datafile/newtest01.dbf';

Database altered.

That’s all there is to it.  Datafile moved/renamed in one step while a table it contained was locked.

SQL> select file_name from dba_data_files where file_name like '%newtest%';



  1. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    SAS Training in Chennai| SAS Course in Chennai

  2. Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
    selenium training in chennai

  3. I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here. Thanks once more for all the details.

  4. This comment has been removed by the author.

  5. Move Oracle Datafiles in NOARCHIVE MODE

    Stop the database:

    SQL> shutdown immediate;

    Copy the datafile to the new directory :

    $ cp /u01/data/datafileD1.dbf /u02/data/datafilenew.dbf

    Mount database

    SQL> startup mount;

    for Full solution please visit below link;

    How to move Datafiles Proper Way

  6. Hey, would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would enjoy your content
    fire and safety course in chennai


  7. Very useful and informative content has been shared out here, Thanks for sharing it.
    Visit Learn Digital Academy for more information on Digital marketing course in Bangalore .

  8. I just needed to record a speedy word to express profound gratitude to you for those magnificent tips and clues you are appearing on this site.

    Tableau Training in Chennai
    Tableau Training

  9. Amazing Article ! I have bookmarked this article page as i received good information from this. All the best for the upcoming articles. I will be waiting for your new articles. Thank You ! Kindly Visit Us @ Coimbatore Travels | Ooty Travels | Coimbatore Airport Taxi | Coimbatore taxi

  10. You are doing a great job. I would like to appreciate your work for good accuracy
    Selenium Training Institute in Chennai | Selenium Testing Training in chennai

  11. The article is so informative. This is more helpful for our
    software testing training institute in chennai
    selenium training Thanks for sharing

  12. Most Poplar Free Porn Training Center largest The coolest Free Porn Videos & Sex Movies Updated Daily. Update is a tube porn site with millions Online and offline real life porn cam

  13. Thanks for sharing this useful information.
    Python class

  14. Nice article
    Visit for Data Science training in Bangalore:
    Data Science training in Bangalore

  15. I’m holding VMware certificate in my hand just because of Pass4sure VMware dumps. I just cleared my final exam in very first try and I wasn’t enough sure before visiting Dumpspass4sure about my success but with that study guide and guidance of experts I attained successive marks in my final. I was attempting that important paper and I didn’t know anything about exam pattern thankfully I got complete guidance and study material to pass my final exam in very first go. I will keep using and recommending this website to everyone who wants to pass exam with surety.

  16. I gathered a lot of information through this article.Every example is easy to undestandable and explaining the logic cloud platform training in bangalore