Re: coalescing free space

Chuck Hamilton wrote:
>
> I remember reading somewhere that oracle is supposed to automatically
> coalesce free space if the blocks are contiguous. In other words if I
> have 3 10m spaces in the same datafile that are immediately next to
> each other, it's supposed to create one 30m space out of it. Does this
> work correctly in version 7.1? I have just such a situation but it
> never seems to coalesce the space. I even tried creating a 30m table
> in that datafile thinking that maybe it only happens when you create
> an object, but it didn't do it then either. What do I need to do to
> coalesce the space, short of recreating the tablespace?

The SMON background process will automatically do this for you UNLESS your tablespace's PCTINCREASE is set to 0, in which case it will NEVER coalesce your free space. You can force Oracle to coalesce free space by issuing the SQL command:

SQL> alter session set events 'immediate trace name coalesce level X';

where X is the tablespace # from the ts$ data dictionary view.

Brian

-- 
Brian M. Biggs                             mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.                       voice: (513) 677-7661
http://www.cincom.com/