Search

Top 60 Oracle Blogs

Recent comments

shrink

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.

How much free space can be reclaimed from a segment?

By Franck Pachot

.
You have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 to 75% free space) is supposed to have at least 50% of free space. Of course it can have more, but you don’t know.