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

Thread: Alter table MOVE command

  1. #1
    Join Date
    Dec 2001
    Posts
    337
    Hello,

    I have just found out that users have created tables on the system tablespace. If i use the ALTER TABLE xxx MOVE TABLESPACE xxx command to move these tables to a diferent tablespace, what are the implications of the constraints and indexes on the respective tables?? Do i have to disable constraints and rebuild indexes??..The indexes are situated in their default tablespace.

    Many Thanks
    Chucks_k

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    ALTER TABLE MOVE will invalidate the indexes, but not the constraints. Once you move the tables, you will have to ALTER INDEX REBUILD the indexes...
    Jeff Hunter

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Thanks for the prompt reply jeff!!

  4. #4
    Join Date
    Sep 2000
    Posts
    305
    Well from this comand your tables will get transfered but the index won't get transfered so you have to rebuild all the Indexes.

    Shailendra

  5. #5
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Originally posted by shailendra
    Well from this comand your tables will get transfered but the index won't get transfered so you have to rebuild all the Indexes.

    Shailendra
    Hi,
    This is not exactly the reason why indexes get 'UNUSABLE';
    The reason is that in the index-leafs where the rowid points to the rows in the table-block get invalid, because the table-block has moved.
    If you would move the index-blocks too the rowid would still be invalid;

    But if there would be an additional clause : INCLUDING INDEXES STORAGE ...

    this would be great indeed.

    Orca

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