Top 60 Oracle Blogs

Recent comments


Free space requirements for ALTER TABLE

One of the very cool features from 12.2 onwards is that the vast majority of segment operations the require rewriting the existing data can be done online without interruption to service. But whether an operation is being done online or not, since DDL operations need to be atomic, the general rule is that when an object is being reorganised this work cannot be done in place. The reorganised segment must be written elsewhere in order for the original segment to be left untouched in the event of the operation failing midway. (No-one wants their table to vanish just because someone pulled out the wrong power cable!)

General consensus from the “blog-sphere” is that you thus need enough space to hold the existing segment and space for the new segment to be created, but I thought I’d explore that a little further, and there’s a few surprises.

Advice on fragmentation and shrinkage

If you have performed some sort of data cleanup or similar on a table, then the deleted space will be reused by future insertions. But if

  • that cleanup was the last task you were performing on that table, ie, you were not expecting a lot of new data to ever come in again, or
  • you are performing a lot of full scan queries on that table and you want to make sure they are as efficient as possible

then there may be benefits to performing a shrink on that table to reclaim that space. One of the cool things about the segment advisor is that it will detect if there are some benefits to be gained by shrinking a segment. Here’s an example of that. I create a large table and then delete every 2nd row.