Re: Help coalescing free space

From: Nadira Gangadhar <gangadnx_at_jmu.edu>
Date: 1997/10/27
Message-ID: <632blb$2fo@owl.jmu.edu>#1/1

Here is a script I got from Oracle Support (note the disclaimer though), and it
proved to be very beneficial to me.

REM SET ECHO ON
REM NAME: TFSCOAL.SQL
REM USAGE:"@path/exec tfscoal"
REM

---

REM REQUIREMENTS:
REM ALTER SESSION, EXECUTE on DBMS_SQL, SELECT ANY TABLE REM granted explicitly
REM

---

REM PURPOSE:
REM To force a coalesce of contiguous free extents. REM

---

-

REM EXAMPLE:
REM SQL> exec tfscoal

REM     Tablespace 2 -- coalesced 1 extents.
REM     Tablespace 5 -- coalesced 59 extents.
REM     Tablespace 5 -- coalesced 59 extents.
REM     Tablespace 6 -- coalesced 21 extents.

REM

---

-

REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM

---

REM Main text of script follows:

set serveroutput on

DECLARE
tsid number;
tsname varchar(30);

cnt     number;
lev     number;
dummy   integer;
cur     integer;

nothing boolean := TRUE;

cursor contig is

      select c.ts#,
             c.name,
             count(*)
      from sys.fet$ a,
           sys.fet$ b,
           sys.ts$  c
      where a.ts# = b.ts#
        and a.ts# = c.ts#
/*        and c.name not in ('TEMP' , 'DWDIFF', 'RBS') */
        and a.file# = b.file#
        and (a.block#+a.length) = b.block#
        group by c.ts#,c.name;

begin
open contig;
  while TRUE LOOP

        fetch contig into tsid, tsname, 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 '||tsname||' ('||tsid||
          ') -- coalesced '||cnt||' extents.');

  end loop;
  close contig;
  if (nothing) then

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

---

Hope it helps!

Nadira Received on Mon Oct 27 1997 - 00:00:00 CST