Seeking Oracle out of the matrix

Google
 
Web aboutoracle.blogspot.com
Oracle New Articles
Oracle Critical Patches
Oracle jDeveloper News
Oracle Blogs of interest


This blog records my personal experience.
Latest news from the front
               

Thoughts about oracle Author's note : Thank you all for your supporting letters. You have been a motivation to this web spot's posting. I wish you all the very best for you and your families for the new year.
Interested to know about a next post? You can suscribe to my RSS feed
HOW TO:

TIP: How to move a datafile from one hard disk to another (safe way)
Thursday, February 22, 2007
I had a test server with 60Gb Hard disk drive. Database was updated thoroughly and space run out quickly. What i did was to install a second hard disk drive and create there another folder to store my moved datafile.
So in order to move c:\mydb\users.dbf to e:\mydb\users.dbf here are the steps to do it safely :


  1. open sql worksheet and connect to the database as sysdba with the sys account,

  2. make sure that all users are out of the database and that no pending transactions are taking place, then run
    shutdown immediate;

  3. execute
    start mount;

  4. copy-paste the datafile you wish to move, to the new location,

  5. execute
    ALTER DATABASE RENAME FILE 'C:\MYDB\USERS.DBF' TO 'E:\MYDB\USERS.DBF';

  6. then execute
    ALTER DATABASE OPEN;


...and you're done.

Easy, isn't it? :)

Labels: , , , , ,


posted by Admin @ 1:28 PM  
add to del.icio.us Digg it! Furl this! add to reddit! add to dzone!
8 Comments:
  • At 8:33 PM, Anonymous Anonymous said…

    You don't have to take database offline to change the datafile name. Just take the specific tablespace offline, rename the file (os and dict) then online the tablespace! Done!!!

     
  • At 12:23 AM, Anonymous mrothouse said…

    Good tip John. Metalink Note 115424.1 - "How to Rename or Move Datafiles and Logfiles" has similar information on moving datafiles with the database open.

     
  • At 12:58 AM, Anonymous jGaL said…

    @Anonymous
    I am not so sure about that. I will check it and repost


    @mrothouse
    Thank you my friend. Although i read the Oracle Database 9i administration relative section about that, thank you for the valuable info

     
  • At 4:36 AM, Blogger Jeff Hunter said…

    Wow, that's by far the most intrusive way to do it. You don't need to bring the db down, you can just:
    alter tablespace users offline;
    !cp /u01/oradata/mydb/users01.dbf /u02/oradata/mydb/users01.dbf
    alter database rename file '/u01/oradata/mydb/users01.dbf' to '/u02/oradata/mydb/users01.dbf';
    alter tablespace users online;

     
  • At 9:45 AM, Blogger JGal said…

    @Jeff
    Jeff, i used the safest way possible for a database of version 9.2.0.1

    Yes this isn't the best way but it is the safest in my point of view.

    @mrothouse
    Thanks for the tip. Really usefull info.

     
  • At 3:00 PM, Blogger Emma Chow said…

    You guys have got a lot for me to learn from. I'm new in this field, but I'll catch up and make difference like you.
    First, I have to try hard to understand you guys' discussions.
    Bless me...

     
  • At 9:04 PM, Blogger DJ mOr€No & DJ kOrVi said…

    olaa

     
  • At 4:03 AM, Blogger 文章 said…

Post a Comment
<< Home

About Me

Name: John Galanopoulos
Home: The NeverLands
About Me: A source code wonderer since the early 80s with my first ZX81 by Sinclair, home computer.
See my complete profile
Previous Post
Archives
Links
ΣΚΛΗΡΥΝΣΗ ΚΑΤΑ ΠΛΑΚΑΣ - ΕΓΚΕΦΑΛΟΣ - ΕΓΚΕΦΑΛΟΓΡΑΦΗΜΑ - ΑΝΟΙΑ - ΝΕΥΡΟΛΟΓΟΣ - ΨΥΧΙΑΤΡΟΣ - ΛΟΙΜΩΔΗΣ ΜΟΝΟΠΥΡΗΝΩΣΗ - ΠΑΡΚΙΝΣΟΝ - ΑΓΧΟΣ - ΚΑΤΑΘΛΙΨΗ - ALZHEIMER - EPSTEIN BARR Eurolife
Email notification

Enter your email address and get notified whenever there is a new post:

Delivered by FeedBurner

This website abides by a strict policy : no spam, just posts; and that's a promise.

Powered by

Free Blogger Templates

BLOGGER


Register for a skinnyscore at www.blogskinny.com and increase traffic
Software Blogs -  Blog Catalog Blog Directory

© 2005 Seeking Oracle out of the matrix Template by Isnaini Dot Com