Moving Indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Moving Indexes

  1. #1
    Join Date
    Jan 2002
    Posts
    58
    I need the syntax for moving an indexes from one tablespace to another. I am taking on Oracle DBA responsibilities and i am discovering massive problems. The version of Oracle is 8.0.4.0

    thanks


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    alter index xyz_pk rebuild tablespace new_ts;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2001
    Posts
    290
    Will the above syntax is OK , if the space is not enough on the disk. Or is there any other way to move the indexes without rebuilding them, because of lack of space.

    ????????????????????

  4. #4
    Join Date
    Sep 2001
    Posts
    261
    mrvajrala,

    You can DROP and re-CREATE the index.


    antilles,

    I recently move all my INDEXES into a new tablespace. I used the following procedure:

    1. Connect as the owner of the Indexes that you want to move
    2. Set Lines n (depending on the number of index)
    3. Spool filename.sql
    4. SELECT 'ALTER INDEX ' || index_name || ' REBUILD TABLESPCE new_tablespace;' FROM user_indexes;
    5. @filename.sql

    I hope this helps =)

    The Maverick
    Oracle Certified DBA - 8i

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Keep in mind, if you don't specify the storage paramaters, the index will keep it's origional settings. Even ignoring the Tablespace Defaults.
    OCP 8i, 9i DBA
    Brisbane Australia

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