If it's a worry about space, have a look at ALTER INDEX COALESCE:
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
I concur. I've found coalescing especially helpful to free up blocks with sequential indexes where alot of deleting is going on.
I have a locking problem when coalescing a table with alot of inserts going on at the same time. When it gets to the end of the index blocking locks will bring the db to its knees.
Oracle support swears it can't happen, but I can reproduce a lock-down every time. I have to be careful to only run the coalesce for 8 or 10 hours then kill it. Archiving goes crazy after a few hours. That's when I know it is actually merging blocks.
Rebuild or Coalesce only for sequence keys.. and ofcourse where history data is purged regularly.. else no use..
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
yeah but LMT's do that implicitly anyway
Originally Posted by DaPi
I'm stmontgo and I approve of this message
talking about indexes here, not tablespaces
Originally Posted by stmontgo
Click Here to Expand Forum to Full Width