Re: coalescing free space

In article <4dlqbf$k5c_at_ixnews8.ix.netcom.com>, chuckh_at_ix.netcom.com (Chuck Hamilton) writes:
|> 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?
|> --
|> Chuck Hamilton
|> chuckh_at_ix.netcom.com
|>
|> If at first you don't succeed, skydiving isn't for you.
|>

As of 7.1 Oracle does not coalesce contiguous free space if pctincrease is set to 0. You can coalesce the space by issuing

alter session set events 'immediate trace name coalesce level <tablespace#>' ;

The number of the tablespace can be retrieved from the sys.ts$ table.

Ian MacGregor
Stanford Linear Acclerator Center
ian_at_slac.stanford.edu Received on Fri Jan 19 1996 - 00:00:00 CET