Re: defragmenting free space in a tablespace

In article <480vjf$27v8_at_usenetw1.news.prodigy.com>, DGWW16A_at_prodigy.com (Glenn Andrews) says:
>
>I called Oracle about the same problem a couple of days ago and they said
>that SMON will not defragment the free space in any tablespace which has
>the default storage parameter "pctincrease" set to 0. I set the
>pctincrease to 1 and the fragmentation was gone in a frew hours. Oracle
>7.3 will add an alter tablespace command to defragment.
>

As an alternative issue the following command while connected as sys

alter session set events 'immediate trace name coalesce level n';

where 'n' is the tablespace number you get from the following query

   Select ts#,name from ts$;

Got this from Oracle Support too (the Canadian one if that matters) and it does coalesce adjacent chunks of free space into bigger contiguous chunks of free space.

Jeff Herrick
Jeff Herrick & Associates Received on Mon Nov 13 1995 - 00:00:00 CET