Re: Fragmentation of Datafile

The tablespace coalesce command suggested in another reply will not help if the free extents are not contiguous. To create usable free space I would suggest you map the tablespace by doing a union of the segment_name, bytes, file_id, block_id columns of the dba_extents and dba_freespace dictionary tables. Then selectively recreate the objects that if moved to another open stop or tablespace will create contiguous free extents that can be combined.

--mapping SQL
select 'Free Space' owner, ' ' object, file_id, block_id, blocks from sys.dba_free_space
where tablespace_name = upper('&&TS')
union
select substr(owner,1,20), substr(segment_name,1,32), file_id, block_id,

       blocks
from sys.dba_extents
where tablespace_name = upper('&&TS')
order by 3, 4;

Harald Stefan <stefan_at_grandel.de> wrote in article <352887DE.BD29203D_at_grandel.de>...
> I have 200 M free space in the datafile. But the fragmentation of the
> datafile ist high, so that
> the largest fragment is only 10 M. Does anybody know a method to reduse
> the fragmentation,
> without a complet export/import.
> The database is Oracle V7.2.2.3 on a RS/6000.
>
> Thanks
>
> Harald Stefan
>
>
Received on Thu Apr 09 1998 - 07:41:39 CDT