Re: coalescing free space

A copy of this was sent to Richard <richardbaines_at_netscape.net> (if that email address didn't require changing) On Thu, 01 Jul 1999 10:38:08 +0100, you wrote:

>Hi
>
>I`m trying to understand the idea of coalescing free space and a few
>things are unclear.
>I have a tablespace with 66 free space 'chunks' (from dba_free_space). I
>was under the impression that if I ran the 'alter tablespace name
>coalesce' command then these free space areas would be coalesced and
>reduce in number, which isn`t the case. Does coalesce simply mean that
>oracle now knows where all the free space is and can use it more
>efficiently rather than reducing all the free space into larger chunks.
>If I want to reduce the number of free space chunks would this be
>achived by exporting and reimporting some of the schema objects or would
>a full export of the tablespace be required.
>

coalescing free space simply takes adjacent sets of free chunks and combines them into 1 chunk. Typically SMON does this in the background on its own but you can use the coalesce command to do it right away.

It is not a defrag tool.

if you really really wanted to reduce the number of free chunks, you would have to exp, drop, imp the objects. Or, if the objects are indexes, alter index rebuild into another tablespace (or just drop them), coalesce the now empty tablespace and rebuild the indexes back into this tablespace.

In any case, think whether you really really want/need to do this. Its alot of work that typically doesn't need to be done. Of course, if you are going to drop and recreate objects and move things around -- make sure you have good backups (just in case)

>many thanks for any assistance on this subject
>
>regards
>Richard

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 01 1999 - 06:33:56 CDT