Re: Help coalescing free space

In article <344F2C2A.22C01EBE_at_netway.at>,

   Norbert Ferstl <ferstl_at_netway.at> wrote:
>Fuzzy wrote:
>>
>> Hi people,
>>
>> Once again I'm suffering from the joy of Oracle's space management
>> techniques (or lack thereof). I've got a 7.1 database, with 700 Mb
>> free, but cannot create a table with initial of 250M and next 100M
>> because the freespace is fragmented. Is there a coalesce free space
>> option in 7.1?
>>
>> Ciao
>> Fuzzy
>> :-)
>
>ORA 7.3 has a coalesce option, ALTER TABLESPACE X COALESCE,
>its not available in 7.1
>
>Oracle trys to coalesce it from a background process (forgoten which)
>but I dont know if its true in 7.1

It's SMON that does the coalesce job, but just like the alter tablespace ... coalesce option, it only coalesces adjacent free extents. This does not help you, since Oracle will also try to coalesce adjacent free space before it fails to create your table.
You have to set the pctincrease default value of the tablespace to a non-zero value to make SMON do the coalesce job.

>Dont worry to create large tables with more but smaller extents,
>it doesnt really impact performance.

Except for the possibility that you will end up having your free space even more fragmented.

>export/import is your last chance, if you can find some tables/indexes
>that slices free-space you can save time in just exporting this
>few tables (look in DBA_EXTENTS)

If you have enterprise manager running on some other database, you can easily identify these tables.
I still can't believe Oracle has no tools to do the job. Even an option in the export utility to export an entire tablespace would be very helpful.

Stefan.

--
Name      :G.R.S. Deisz
Phone     :+31-50-5855954
E mail    :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
DISCLAIMER:This statement is not an official statement from, nor
           does it represent an official position of, PTT Telecom BV.