Moving Tablespace/data file
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Moving Tablespace/data file

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi there,

    We are on 8.1.7.3.0 / Windows NT.

    I want to move my Index tablespaces from E: drive to D:.

    Which is the best option available on above version to accomplish my task?

    Thanks in Adv.




    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Oct 2001
    Posts
    83
    Hello,

    An option consist of rebuilding indexes :

    You have to create the new TS;

    Generate a script with alter index orders :


    ...
    alter index index_name rebuild tablespace_name;
    ...


    Hope this helps


  3. #3
    Join Date
    Mar 2002
    Posts
    6
    Take your index tablespace offline. Copy the datafiles that belong to the tablespace to your new destination. Rename the datafiles to *.old (by renaming instead of deleting the datafiles allows you to recover if you goof). Run sql command ALTER TABLESPACE rename datafile 'OLD DATAFILE' to 'NEW DATAFILE' ; for each datafile in your tablespace. Put the index tablespace online. Check your datafile names from v$datafiles to make sure they all have the path that you want. If everything looks good. go ahead and delete your *.old files.

  4. #4
    Join Date
    Mar 2002
    Posts
    301
    1. make TBS offline
    2. Copy the datafiles to new Drive
    3. alter database rename datafile 'old location' to 'new
    location'
    4 . make TBS online

    sbensmail@yahoo.fr

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Great quick replies from you great guys!

    I would implement it and come back if needed.

    Thanks
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    When at the end, I say


    alter database datafile 'Filename at new location' online;

    It gives me foll. error.

    ORA-01113: file 40 needs media recovery
    ORA-01110: data file 40: 'File name'

    Then, from svrmgr> I recovered the datafile and then it allowed me to make it online.

    So, what care should be taken so that I do not get above error in future?

    I did not close the database while renaming the datafile. Is that OK?







    [Edited by samdba on 07-02-2002 at 11:31 AM]
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Mar 2002
    Posts
    6
    I have seen that happen when there are uncommited transactions that would have been written to an object contained in the tablespace. You can avoid this by

    A: Disable user access by stopping the listener. Force a checkpoint, force a log switch, force another checkpoint. Yeah it's overkill but......

    OR

    B: Shutdown the database so that everything gets committed and checkpointed.

  8. #8
    Join Date
    May 2002
    Posts
    232
    Hiiii,noor this is only public weleducated forum.
    But it is not religious forum.
    I am very sorry to say,don't advtise relegious matters in this forum.
    plsss i am requesting to all users,noor given religious link,plss don't accept this type of malpractice.

    noor link http://www.islamway.com/eng/html/invitation.php

    kavitha

  9. #9
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Have to Agree with kavitha. Plz dont use such kinds of links in this forum.
    -- Dilip

  10. #10
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    The database must be in mount state when you are using
    ALTER DATABASE RENAME FILE .....

    I think that is the reason you had to do a media recovery.

    Sanjay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width