Coalesce Tablespace Question

Okay, we have just coverted some tables with longs to tables with clobs. We had the chance so we put the clobs in a seperate tablespace. In the conversion, we basically copied the production table to a new one with different name converting to clobs, then renamed them. Anyway, in the end we have several tables(10 to 20Gb each) that will be dropped as they are no longer needed. My understanding is that oracle will automatically coalesce that space, if any of the chunks that are freed are contiguous. If not then the alter tablespace coalesce command will force it. Otherwise if the space is not contiguous, then oracle will only use the chunks if all an objects next extent will fit with in the chunk.

I guess I'm just checking to see if there is anything wierd with this situation I should be aware of. Do I need to make any temporary changes to %free or %used parameters for the tablespace just before dropping them. I am assuming oracle only allocates whole blocks when it extends an object, so the %free or %used wouldn't be useful as the entire block will be freed and put back on the free list.

Any comments or experience here is appreciated.

Tom Crider
Senior DBA
Fundsxpress Financial Systems, Inc
thom_at_fundsxpress.com Received on Wed Apr 18 2001 - 15:40:18 CDT