Tablespace Fragmentation : Causes and Cures 

Fragmentation of a tablespace occurs when lots of pockets of free space are available within a tablespace, but none of them are of a sufficient size to house new extents for segments. 

Imagine that we are going to create the following tables within a tablespace: 

EMP - Initial Extent Size 4 blocks, Next Extent Size 5 blocks DEPT - Initial Extent Size 3 blocks, Next Extent Size 4 blocks 

We issue those commands, one after the other, and what do we have?

(EMP is green-ish, DEPT is orangey). 

Now we start loading data into DEPT. It runs out of space in its initial extent, and has to acquire some more, and it does that a couple of times. Now our tablespace looks like this: 

(One Initial, followed by 2 next extents of 4 blocks each). 

At this point, we now start loading into EMP, and it acquires some extra extents to accommodate the data, too: 

Now we decide that DEPT would be better off in some other tablespace altogether, so we move it (or drop it and re-create it elsewhere).That frees up the 11 blocks that DEPT was using, like this: 

be. So that murky chunk in the middle of the tablespace is actually comprised of a piece 3 blocks big, followed by two pieces of 4 blocks each.That's important, because if EMP needs to acquire another extent of 5 blocks, where is it going to be able to get it? 

Er, ... at the END of the tablespace. Whilst there are 11 blocks going begging in the middle of the tablespace, the retention of the old extent boundaries means that it looks like a 3+4+4... no one piece of which is able to accommodate a new 5-block extent for EMP.Now if EMP wanted to acquire one final 5-block extent, can it do so?Clearly, the answer is no... there are just two blocks at the end of the tablespace, and the old 3+4+4 in the middle. None of that allows for a new 5-block extent to be allocated.So even though there are 13 blocks going free, none of them can actually be used. The User inserting the new record that is provoking EMP to extend will instead get a message to the effect that Oracle is "unable to allocate next extent in tablespace BLAH", and the insert will fail. 

And that's fragmentation. Lots of free space, none of it in large enough contiguous chunks, able to be used. Fragmentation is NOT a performance issue. This is not like the sort of fragmentation you get with NTFS or FAT32 which really can slow down a machine to a crawl. Fragmentation is purely a question of using space efficiently, and not wasting it. 

What did we do to make this happen? Firstly, we freed up extents. Had we not dropped (or moved) DEPT, that chunk of 11 blocks in the middle of the tablespace would have been in effective use, and there would have been no pockets of free space to worry about.Of course, EMP would still have failed to extend at the end, but that's just a question of running out of space in a tablespace, not of fragmentation. So, if you never, ever drop tables or indexes, move tables, rebuild indexes or truncate tables, you needn't worry about fragmentation -except that the chances of you never needing to do one of these things is zero! 

So given that we can't totally avoid ever freeing up extents, what else caused this problem? Simply, that our original table definitions permitted EMP and DEPT to acquire odd-sized extents. Had both tables always come in, say, 5 block extents, then of course EMP would have been able to re-use one of the ones freed up when we re-located DEPT.

Which brings us to how you prevent fragmentation happening in a tablespace in the first place: ALWAYS use consistent extent sizes within a given tablespace. Make sure that all segments within tablespace DATA all have INITIAL equal to NEXT, and all using the same values for both parameters. That way, fragmentation can simply never happen. Preventing odd-sized extents being created is not easy, though. 

In Oracle 7, you can use a 'default storage clause' at the tablespace level to say what you would like segments to use as their INITIAL and NEXT settings.Unfortunately, if someone specifies an explicit storage clause when creating, say, a table that completely overrides the tablespace's default storage clause. And there's nothing you can do about it. 

In Oracle 8, you can use a 'minimum extent' clause to specify that extents should always be of that size, or a multiple thereof. And that cannot be over-ridden by something specified when creating a table.With a minimum extent of 500K for the tablespace, if someone tries to create a table with, say, an INITIAL of 223K and a NEXT of 398K, then that table will actually acquire a 500K initial extent, and its next extent will also be 500K. Two 500K extents starts sounding like consistent extent sizes, and thus no fragmentation.Unfortunately, it's not perfect: if the table had a next extent size of 724K, then it will actually acquire a 1Mb next extent -because 'minimum extent' means 'minimum extent or multiples thereof' -and the nearest multiple of 500K when you've asked for 724K is 1000K, or 1Mb. So you've still got odd-sized extents, and the possibility of eventual fragmentation. 

Fortunately, in 8i, there's a perfect cure. You create your tablespaces as "locally managed" with a 'uniform size' clause. Nothing can over-ride the uniform size clause, and you don't get multiples of it, either. Set it to, say, 500K, and that is the ONLY size the tablespace can dispense. If you now ask for an initial extent of 724K, then you will be allocated two extents of 500K each.Ask for 1398K, and you'll get 3 500K extents, and so on. And since every extent is always going to be 500K, you've now got entirely consistent extent sizes, and zero possibility of ever encountering fragmentation. 

One more thing to say about this idea of consistent extent sizes: it does mean that you need to plan your tablespaces more carefully. If all you had was a tablespace allocating 500K extents, it would be a bit sad to then use it to house the "States and Territories of Australia" lookup table (which happens to consist of 8 records).That would be a mammoth waste of space -which is all fragmentation itself is at the end of the day.What you really need, of course, is a range of tablespaces, some allocating 16K extents, some 64K, some 256K, some 512K and so on.Then all you have to do is house the right segment in the right sort of tablespace. CUSTOMERS in the 512K tablespace; SALES in the 10Mb tablespace; COMPLAINTS in the 16K tablespace (at least, I hope you don't get too many complaints!), and so on. 

Now let's assume that you didn't know any of all this, and you've housed your tables all over the place, with wildly differing extent sizes, and you're worried you’ve got fragmentation. Two questions arise: how do you know you’ve got fragmentation, and how do you cure it? 

Diagnosis is relatively easy. If you issue the command SELECT COUNT(*), MAX(BLOCKS) FROM DBA_FREE_SPACE WHERE TABLESPACE = 'BLAH'; you'll be counting the total number of pieces of separate free space in a tablespace, and seeing the size of the single biggest piece.If count(*) goes through the roof, but max(blocks) is trivially small, that's a fairly good indication of fragmentation -lots of little pieces of free space.You can then query the dba_free_space view without the aggregating functions to make absolutely sure. 

(Incidentally, if you're using Oracle Enterprise Manager, the Tablespace Map utility gives a nice visual display of extent allocations within a tablespace, and fragmentation leaps out at you as lots of little bits of white space). 

Curing it is tricky. You can always try this command: ALTER TABLESPACE BLAH COALESCE; That removes extent boundaries from contiguous pieces of free space, thus converting lots of small pieces that can't be re-used into a giant contiguous chunk that can be.If we did that to the tablespace described earlier, we'd get this sort of picture after doing a coalesce: 

Notice how the 3+4+4 has become one long piece of 11 blocks. That would allow EMP, requesting a 5 block extent, to acquire it from that piece of free space.Notice what coalesce didn't do, though: It did not merge the 11-block chunk with the 2-block chunk at the end to generate a single 13-block piece of free space.Coalesce only merges adjacent pieces of free space. 

If you wanted to achieve that sort of complete de-fragmentation, about the only realistic option is to export EMP with COMPRESS=N, truncate it, perform a coalesce and then re­import with IGNORE=Y. That would indeed achieve the following sort of result: 

Of course, having to truncate the EMP table means it's unavailable for use by anyone until the import has finished, so it's not exactly a high-availability solution. 

Which means that we should recognise that preventing fragmentation from happening in the first place is infinitely cheaper than seeking to fix it up afterwards. And the rules for preventing it are easy: use consistent extent sizes within a tablespace. (And that in turn means, if you've any sense, and are using 8i, use Locally Managed tablespaces, because with them, fragmentation simply cannot happen).