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%';

FILE_NAME
--------------------------------------------------------------------------------
/oracle/base/oradata/TEST1/datafile/newtest01.dbf

41 comments:

  1. 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.
    google-cloud-platform-training-in-chennai



    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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

    ReplyDelete

  5. 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 https://www.learndigital.co/ .

    ReplyDelete
  6. 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

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

    ReplyDelete
  8. Nice article
    Visit for Data Science training in Bangalore:
    Data Science training in Bangalore

    ReplyDelete
  9. 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.

    ReplyDelete
  10. Great efforts put to find the list of articles that are very useful to know. I’m thoroughly enjoying your blog. And Good comments create relations. You’re doing great work. Keep it up.

    Magento Development Training Course in Chennai Zuan Education

    Selenium Training Course in Chennai Zuan Education

    ReplyDelete
  11. You are doing a great job. I would like to appreciate your work for good accuracy
    Regards,

    7 tips to start a career in digital marketing

    “Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

    we have offered to the advanced syllabus course digital marketing for available join now

    more details click the link now

    https://www.webdschool.com/digital-marketing-course-in-chennai.html

    ReplyDelete
  12. Thank for this blog is more informative step by step useful contents. I here by attached my site would see this blog

    Web designing trends in 2020

    When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, Whats App, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.

    we have offered to the advanced syllabus course web design and development for available join now

    more details click the link now

    https://www.webdschool.com/web-development-course-in-chennai.html

    ReplyDelete
  13. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
    Your post is just outstanding! thanks for such a post,its really going great and great work.You have provided great knowledge about thr web design development and search engine optimization
    Java training in Chennai

    Java Online training in Chennai

    Java Course in Chennai

    Best JAVA Training Institutes in Chennai

    Java training in Bangalore

    Java training in Hyderabad

    Java Training in Coimbatore

    Java Training

    Java Online Training

    ReplyDelete
  14. This article is so informative and intersting really amazing job.
    https://www.acte.in/reviews-complaints-testimonials
    https://www.acte.in/velachery-reviews
    https://www.acte.in/tambaram-reviews
    https://www.acte.in/anna-nagar-reviews
    https://www.acte.in/porur-reviews
    https://www.acte.in/omr-reviews
    https://www.acte.in/blog/acte-student-reviews

    ReplyDelete
  15. Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.Very useful and informative content has been shared out here, Thanks for sharing it
    Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.Very useful and informative content has been shared out here, Thanks for sharing it

    Azure Training in Chennai

    Azure Training in Bangalore

    Azure Training in Hyderabad

    Azure Training in Pune

    Azure Training | microsoft azure certification | Azure Online Training Course

    Azure Online Training

    ReplyDelete
  16. Very informative post! There is a lot of information here that can help any business get started with a successful social networking campaign.

    ReplyDelete
  17. Very informative post! There is a lot of information here that can help any business get started with a successful social networking campaign.
    IELTS Coaching in chennai

    German Classes in Chennai

    GRE Coaching Classes in Chennai

    TOEFL Coaching in Chennai

    spoken english classes in chennai | Communication training



    ReplyDelete
  18. Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts.
    Data Science Training In Chennai

    Data Science Online Training In Chennai

    Data Science Training In Bangalore

    Data Science Training In Hyderabad

    Data Science Training In Coimbatore

    Data Science Training

    Data Science Online Training

    ReplyDelete
  19. I like your everything post. You have done better than average work. Much obliged to you for the data you give, it helped me a great deal. I would like to have numerous more sections or so from you.
    IELTS Coaching in chennai

    German Classes in Chennai

    GRE Coaching Classes in Chennai

    TOEFL Coaching in Chennai

    spoken english classes in chennai | Communication training


    ReplyDelete
  20. 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.



    AWS Course in Bangalore

    AWS Course in Hyderabad

    AWS Course in Coimbatore

    AWS Course

    AWS Certification Course

    AWS Certification Training

    AWS Online Training

    AWS Training

    ReplyDelete
  21. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    Good to learn about DevOps at this time.
    DevOps Training in Chennai

    DevOps Course in Chennai

    ReplyDelete
  22. Thanks for sharing nice information.
    Also check python training in bangalore

    ReplyDelete
  23. UPSC Full Form is very usefull in comptition students

    ReplyDelete

  24. Your post is very nice,i like it very much....ankit saini

    ReplyDelete