Re: Coalesce Tablespace Question

In article <3ADDFBB2.E7607111_at_yahoo.com>, TMAN <shadow_man_600_at_yahoo.com> writes:
>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
>

The table parameters pctused and pctfree do not even enter the equation as you are dropping the table so all extents allocated to the tables will be returned to sys.dba_free_space (sys.fet$) and removed from sys.dba_extents (sys.uet$) if dictionary management (all ver prior 8.1) is being employed otherwise if you are using locally managed tablespaces (8.1+ only) then the extents will be returned to the tablespace bitmap. I would issue the alter tablespace coalesce, if dictionary managed, to be sure I got the maximum free space coalesce.

• Mark D. Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net