Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Alter table move command

Re: Alter table move command

From: Jack Silvey <>
Date: Tue, 09 Jul 2002 09:24:36 -0800
Message-ID: <>


The alter table move command will invalidate all existing indicies and you will have to rebuild them. This is because the alter table move is implemented as a CTAS in the background, and all of the rowids will change.

table move tips:

  1. use parallelism - however, parallel processes will write to their own segments, and will trim the unused space off the end for all but one of the segments during the final merge of all the segments into the new index segment. This can give you odd sized segments, throwing off uniform space allocation (if you use that).

index rebuilding tips:

  1. If your index is partitioned, you can rebuild the partitions at the same time, and rebuild all of your indexes at the same time. However, you cannot update/insert/delete the table while this is going forward, unless you use the "online" option, which has limitations.
  2. Use parallel (degree x) to rebuild, since it will spawn off more processes and take less time. see space considerations above.
  3. Use nologging, unless you want to store your indexes in the redo logs (otherwise, you can always just recreate, much easier)
  4. use the "compute statstics" clause to gather stats during the build
  5. consider using initrans 4 or better, otherwise, you risk running into deadlocks during parallel updates (doesn't take up that much more space - 23 bytes or so). Same for pctfree - leave 1 pct, otherwise the ITL list can't grow and you might get into trouble.
  6. some indexes can be built online, which allows updates to go forward during the rebuild.



Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
Please see the official ORACLE-L FAQ:
Author: Jack Silvey

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Jul 09 2002 - 12:24:36 CDT

Original text of this message