DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Move tables to different Tablespaces dynamically

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hello:

    What is the best procedure to move tables from one tablespace to another while the system is in production? Is this a good approach or not? what are the risks and waht is the safest way. Can afford downtime...

    Environment is oracle 8.1.7.2.0

    If there is any reading material about this functionality, please point me there as well..

    Thanks, ST2000

  2. #2
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    You'll have to recreate the object with any available methods in the new TBS and then delete the old object. If you are doing in the same schema, you can use "rename" feature.

    Methods include ( you said you could afford downtime)

    Create Table as Select ...
    Import/Export
    Sqlplus Copy Statement
    Insert into .. Select

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    alter table xxx move tablespace zzzz

    but you have to rebuild indexes afterwards because after moving the table thw rowid are different

    also in 8i the table is locked during movement, no DML can be done

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by pando
    alter table xxx move tablespace zzzz
    This is by far the easiest and quickest method to move your tables.
    Jeff Hunter

  5. #5
    Join Date
    Oct 2000
    Posts
    449
    Thanks much for the quick responses

    I am also interested in reading material on the tables movement, coz, some tables are in sizes of GB

    Thanks, ST2000

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    tahiti.oracle.com

  7. #7
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    What Version is this... 9.x ??

    Best part of such message board, share and learn

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    ALTER TABLE MOVE is available in versions >= 8.1.5
    Jeff Hunter

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