Re: Coalesce command for fragmentation

Hello,

The following command:
ALTER TABLESPACE xxx COALESCE;

will coalesce only free extents that are concatenated to each other EXAMPLE:

• USED ***
• USED ***
• USED *** ... FREE ... <----\
• USED *** ---These two will not be coalesced ... FREE ... <----/
• USED ... ... FREE ... <---\___These two will be coalesced ... FREE ... <---/

So coalescing will not really help your fragmentation, since it's not going to get rid of "Free holes" between used space, the only way to get rid of fragmentation is IMP/EXP. FYI: Coalescing can be accomplished automatically by setting PCTINCREASE to non zero. Script below can help you identify "Free Holes" in your tablespace:

--
-- BEGIN set pages 60
set lines 132
set verify off

col file_id heading "File|id"

select 'free space' owner /*"owner" of free space*/
, ' ' object /*blank object name*/
, file_id /*file id for the extent header*/
, block_id /*block id for the extent header*/
, blocks /*length of the extent, in blocks*/
from dba_free_space
where tablespace_name = upper('&&1')
union
select substr(owner,1,20) /*owner name (first 20 chars)*/
, substr(segment_name,1,32) /*segment name*/
, file_id /*file id for the extent header*/
, block_id /*block id for the extent header*/
, blocks /*length of the extent, in blocks*/
from dba_extents
where tablespace_name = upper('&&1')
order by 3,4

• END --

It will prompt you for 1 which is a TABLESPACE_NAME

+-------------------------------------------------------------+
| Vitaliy Mogilevskiy                                         |
| Senior Consultant                                           |
| CORE Technology Group, Inc.                                 |
| E-mail:    vit100gain_at_earthlink.net                         |
| Fax :      (707) 516-2163                                   |
| Web Page:  http://home.earthlink.net/~vit100gain/index.html |
|            *** Free DBA Script Library at my Web Page ***   |
+-------------------------------------------------------------+

Vick wrote:

> I read on Ari Kaplan's web page that you can use
> ALTER TABLESPACE xxx COALESCE
> /
> (where xxx is the appropriate tablespace_name)
>
> to defragment tablespaces instead of exporting, dropping and
> importing.  And that this can be done "on the fly" with users connected.
>
> I inherited a 3 year old DB and it has never been tuned.  It runs, but
> the tables have many extents in the 20's and 30's.  This is extremely
> bad from what I have read so far.
>
> Help would be great or, a re-direct to the source for further research
> material would also be appreciated.
> Thanks very much,
> Vickie
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---