Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Dictionary-managed tablespace

Re: Dictionary-managed tablespace

From: Howard J. Rogers <>
Date: Sat, 13 Jul 2002 19:06:20 +1000
Message-ID: <agoqh3$ce4$>

"EP" <> wrote in message news:agom8j$7fg$
> > The usual caveats apply: PCTINCREASE should always and without exception
> be
> > 0. Not 1, not 100. Zero. Every time. Based on the numbers you are
> reporting,
> > you've got odd-sized extents in that tablespace, and that's a recipe for
> > fragmentation.
> OK, but when you set PCTINCREASE to 0 then SMON doesn't coalesce free
> extents. So you have to do it manually, don't you ?
> Regards
> EP

Yes, if you are daft enough to permit odd-sized extents within the tablespace. But you won't be that daft, will you, so you won't ever need to coalesce. Manually or automatically.

You do, I hope, understand what coalescing is, don't you:

When you drop a segment (or truncate it), the space the segment used to use is still recorded by Oracle as separate pieces of free space. So If you had table A 5 blocks, table B 4 blocks, table A (extent number 2) 5 blocks, table B (extent number 2) 4 blocks, table B (extent number 3) 4 blocks, and then decided to drop table B, you'd have: 5 blocks A, 4 empty, 5 blocks A, 4 empty, 4 empty.

Table A now wants another extent of 5 blocks -and can't get it. Obviously the first 4 blocks' space is too small. But the last two bits of empty space are also, individually, too small. 4 and 4 does NOT mean 8 free blocks, but 4 and 4. Coalescing means merge those last two chunks of free space into one single chunk of 8 blocks. After you coalesce, Table A can now make use of 5 of those 8 blocks.

But if you'd started with: Table A: 5 blocks, Table B: 5 blocks, Table A: 5 blocks, Table B: 5 blocks, Table B: 5 blocks.... then when you drop table B, you've got three pieces of empty space, each of 5 blocks -and Table A can make use of them without any further effort on your part. Consistent extent sizes means you never need to coalesce, because fragmentation (lots of free space, but most of it in small bits and pieces none of which are useable by other segments) never happens.

So zero for PCTINCREASE is the correct answer. Always and forever.

It was, quite frankly, the worst thing Oracle ever invented.

HJR Received on Sat Jul 13 2002 - 04:06:20 CDT

Original text of this message