Row Movement and Shrink space command
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Row Movement and Shrink space command

  1. #1
    Join Date
    Mar 2006
    Posts
    176

    Row Movement and Shrink space command

    I have couple segment that need to be shrink and below is what I am trying to run:

    alter table ........ enable row movement
    alter table ........ shrink space


    According to Oracle, during the short space release phase, any cursors referencing this segment may be invalidated and queries on the segment could be affected. Based on this statement, I don't think I can do it online while my application is still running, am I correct? Any other alternatives for me to run this online while the app is still running?

    Thx

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool Nope.

    Quote Originally Posted by matthew00 View Post
    ... Based on this statement, I don't think I can do it online while my application is still running, am I correct? Any other alternatives for me to run this online while the app is still running?
    Correct, you cannot do it while application is running. The reason being is that the rowid's might change and therefore open cursors are invalidated and indexes may need to re-build.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2006
    Posts
    176
    Am I also supposed to rebuild the index afterward? if I so, I wonder why EM doesn't have rebuild index when it try to move the rowID.

    Thanks,

  4. #4
    Join Date
    Mar 2006
    Posts
    176
    any Comments?

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    http://www.oracle.com/technology/ora...o35tuning.html

    This link suggests that shrink space is performed through a series of deletes and inserts. So, the index should be taken care of automatically. But the point to be noted is that it will induce lot of fragmentation in the index thereby requiring the index rebuild/coalese/etc.
    lucky

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