Re: Coalesce Tablespace in 7.2?

Hey guys,

If you don't mind a little bit of 'strange' syntax, you can use the following command:

alter session set events 'immediate trace name coalesce level TS#';

where TS# is the tablespace number of the tablespace you want to coalesce. You can get the TS# from the sys.ts$ table with

select name, ts#
from sys.ts$
where name = 'TABLESPACE_NAME'

If you are interested, in 7.3 the command is:

Alter tablespace TABLESPACE_NAME coalesce;

There is also a way to do it on 7.1.6, but that is a little more complex...

Steven Seacord and Steve White
Oracle DBA's
AMP, Inc.

Axel Schlueter wrote:
>
> In article <321C7497.1740_at_ionet.net> Dick Allie <dallie_at_ionet.net> writes:
>
> > From: Dick Allie <dallie_at_ionet.net>
> > Newsgroups: comp.databases.oracle
> > Date: Thu, 22 Aug 1996 09:54:15 -0500
> > Organization: Macklanburg-Duncan
 

> > I'm not sure about 7.2 but in 7.1.6 if you set the default pctincrease
> > to a value > zero eg. 1 for the tablespace it will coelesce the
> > tablespace periodically when extents are given back to free space.
>
> If you set pcticrease to 1, ORACLE will coalesce freespace (honeycombs), but
> this 1% increase will cause severe fragmentation because of the many
> different extent sizes you will get. Probably you will see more bubble
> fragmentation. Remember, you cannot delete bubble fragmentation without
> reorganizing your tablespace.
>
> For further information, you should take a look at the white paper 'ORACLE
> Server Space Management' by Cary V. Millsap at
>
> http://www.europa.com/~orapub
>
> Axel Schlueter
> schluete_at_informatik.uni-hannover.de
  Received on Sat Aug 24 1996 - 00:00:00 CEST