Re: 'Coalesce' indexes tablespace pre-7.3

From: Neil Chandler <oracle_at_tchp2.tcamuk.stratus.com>
Date: 20 May 1998 14:40:09 GMT
Message-ID: <slrn6m5qn0.ple.oracle@tchp2.tcamuk.stratus.com>

In article <hBo81.1482$wM5.2884535_at_news.giganews.com>, "GT Jeff Cherer" wrote:

OK, To coalesce contiguous free extents you would, in Oracle 7.3+, use the ALTER TABLESPACE.... COALESCE command. However, prior to that, try this script...

To make things really tight, you could drop the indexes first and then recreate them after the coalesce. If possible, it is good practice to make INITIAL and NEXT extents a small set of mutliples e.g. 512K, 1024K, 4096K, etc. and to try and make the INITIAL extent large enough to accomodate all of the initial data for a fixed time period. Note that indexes do not clean up after themselves very well, especially after splits, so reorganizing them after a large percentage change in the data can be advantageous and improve performance.

regards

Neil Chandler

REM coalesce.sql----

spool coalesce.out

DECLARE
tsid number;
cnt number;
lev number;
dummy integer;
cur integer;
nothing boolean := TRUE;

cursor contig is

        select a.ts#, count(*)
        from sys.fet$ a, sys.fet$ b
        where a.ts#=b.ts#
        and a.file#=b.file#
        and (a.block#+a.length)=b.block#
        group by a.ts#;

begin
  open contig;
  while TRUE LOOP

        fetch contig into tsid, cnt;
        exit when contig%NOTFOUND;
        nothing:= FALSE;
        lev := power(2,16)*cnt + tsid;
        if (lev < 0) or (lev > 4294967296) then
                raise INVALID_NUMBER;
        end if;
        cur:= dbms_sql.open_cursor;
        dbms_sql.parse(cur, 'alter session set events
           ''immediate trace name coalesce level '||lev|| ' ''', dbms_sql.v7);
        dummy:= dbms_sql.execute(cur);
        dbms_sql.close_cursor(cur);
        dbms_output.put_line('Tablespace '||tsid||' -- coalesced '||cnt||
                ' extents.');

  end loop;
  close contig;
  if (nothing) then

        dbms_output.put_line('Nothing to coalesce!');   end if;
end;
/
spool off

>new (to oracle - actually have been on the planet for quite some time) geek
>here.
>
>running 7.1 and have a tablespace that is pure indexes.
>it is running low on freespace and is honeycombed.
>
>well, ya can't export/import a ts; and i can't export/import index objects
>(without going thru trickery and deceipt, you know what i mean). so, i drop
>all the indexes (except a couple of pk's that i need up), and recreate them.
>very minimal effect on the problem.
>
>adding space will put off the freespace problem, but it won't do much for
>the honeycomb problem.
>
>surely, someone has dealt with this situation. please, some advice.
>
>
Received on Wed May 20 1998 - 09:40:09 CDT