Re: coalescing free space

From: <anderson_at_hg.uleth.ca>
Date: 1996/01/30
Message-ID: <4eleta$qoe_at_holly.cc.uleth.ca>#1/1

In article <Pine.SUN.3.91.960119103412.4997E-100000_at_seatimes>, Steve Butler <sbut-is_at_seatimes.com> writes:
>
>On Thu, 18 Jan 1996, Chuck Hamilton wrote:
>
>> I remember reading somewhere that oracle is supposed to automatically
>> coalesce free space if the blocks are contiguous. In other words if I
>> have 3 10m spaces in the same datafile that are immediately next to
>> each other, it's supposed to create one 30m space out of it. Does this
>> work correctly in version 7.1? I have just such a situation but it
>> never seems to coalesce the space. I even tried creating a 30m table
>> in that datafile thinking that maybe it only happens when you create
>> an object, but it didn't do it then either. What do I need to do to
>> coalesce the space, short of recreating the tablespace?
>
>There was a feature mentioned in this group about a month ago. Seems
>that if the DEFAULT pctincrease on a tablespace is 0, then ORACLE does
>not coalesce free space. I've set my default to 1 (% that is) and
>override it back to 0 on each table. This seems to cure the problem
>(where dropping the tables and creating them would run out of space on
>the 2nd or 3rd itteration). Looks like it collects free space only at
>create time.
>
>
>+----------------------------------------------------+
>| Steve Butler Voice: 206-464-2998 |
>| The Seattle Times Fax: 206-382-8898 |
>| PO Box 70 Internet: sbut-is_at_seatimes.com |
>| Seattle, WA 98111 Packet: KG7JE_at_N6EQZ.WA |
>+----------------------------------------------------+
>All standard and non-standard disclaimers apply.
>All other sources are annonymous.
>

Steve is correct, Oracle does not coalesce tablespaces whose default percent increase is 0. I belive this was to prevent the coalescing of the rollbacks and temporary tablespaces, which could have a big performance impact. The solution is two fold. 1) do as was suggested above, i.e. set the default pctincrease for the tablespace to 0, or 2) you can explicitly request a coalesce by issuing an
 "alter session set events 'immediate trace name coalesce level TS'; where TS is the table space number. You can select the tablespace number from the SYS.TS$. TS# is the Tablespace number and NAME is the tablespace name. The following script will give you an idea how I use it.

Sorry for the quick and dirty explanation, I don't have much time right now.

SELECT 'ALTER SESSION SET EVENTS ' || '''' ||                                   
       'IMMEDIATE TRACE NAME COALESCE LEVEL ' || TS# || '''' || ';'             
  FROM SYS.TS$                                                                  
 WHERE TS# > 0                                                                  
   AND DFLEXTPCT = 0                                                            

   AND NAME LIKE UPPER('&1'); Doran Anderson
Technical DBA
University of Lethbridge
Lethbridge, Alberta
Canada

Note: this information came directly from Oracle. If you have access to the Oracle BBS you probably will find this information there. (in BBS Article # 2450 on the Oracle Canada BBS, under TYPE: TIPS) Received on Tue Jan 30 1996 - 00:00:00 CET