Re: Coalescing free space

Keith Lye wrote:
>
> Hi, can anyone tell me whether it's possible to coalesce free space
> while a database is up and running, or do the tables get locked? If it's
> possible, are there any performance penalties? Sorry if this is basic,
> but I just can't seem to find the answer anywhere.
>
> Thanks,
> Keith

You are probably not really asking the question that you really intend to ask. For those of us who have been working with Oracle 8i for a while now the concept of coalescing free space has a particular meaning and involves combining multiple adjacent free segments to just a single combined area of free space within a tablespace.

What you may be asking is if there is a way to completely defragment a tablespace so that all allocated space is contiguously allocation in the tablespace and whatever free space remains would also be located in a contiguous manner. If that is the case, then the answer is a qualified yes and no. Yes, in the sense that it can be done if you can guarantee quiessence of the database objects in a particular tablespace and are free to drop and recreate the objects. No, in the sense that it cannot be done dynamically - it will always involve dropping the existing objects. Received on Thu Jan 04 2001 - 20:57:56 CET