Re: Defragmenting tablespace.

From: Guy Harrison <gharriso_at_werple.apana.org.au>
Date: 12 Jan 1994 09:04:55 +1100
Message-ID: <2gv7q7$c2k_at_werple.apana.org.au>

pihlab_at_cbr.hhcs.gov.au writes:

>Defragmenting tablespace.

>Something strange happened to me at work this morning ...

...

        (Free space fragmentation disappeared in his database) ...

>It would appear that Oracle7 (well 7.0.13.0 at least) actually merges
>adjacent free space chunks when you use the "ORDER BY" and/or
>"WHERE --- LIKE" clause. My database is now defragmented so I can't
>easily test which caused this to happen but I think its the "ORDER BY".
 

>Is this a documented feature? Is it consistent with later releases?

Yes: SMON "also coalesces free extents whithin the database, to make free space contiguous and easier to allocate" (Server concepts manual 1.-9).

>Could somebody check which clause is causing the defragmentation to
>occur and if it works on later releases please.
 

>I had originally planned to build a utility to merge adjacent free space in
>Oracle7 databases but if this works then I just need to automate the
>running of this script.

Looks like you don't need to do a thing! :-}

>--
>Bruce... pihlab_at_cbr.hhcs.gov.au

Regards,
Guy

---

Guy Harrison                        gharriso_at_werple.apana.org.au
ORACLE  Database Consultant         gharriso_at_vitgwms1.telecom.com.au

Synchronous Software P/L
Melbourne Australia Received on Tue Jan 11 1994 - 23:04:55 CET