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?
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.
Originally Posted by matthew00
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
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.
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.
Click Here to Expand Forum to Full Width