Subject:            SQL: DBA - Manually Coalescing Free Space
Creator:            LTO
Modified:           24 Mar 97 05:32:31           



Introduction:
~~~~~~~~~~~~~
	There are a number of reasons why free space coalescing may not be 
	occuring on a given system. This short note describes how to manually
	coalesce one or more tablespaces.

In Oracle 7.3:
~~~~~~~~~~~~~~
	ALTER TABLESPACE tablespace_name COALESCE;

Oracle 7.1 and 7.2:
~~~~~~~~~~~~~~~~~~~
	You can enter and use the script below to coalesce space:

REM  Needs ALTER SESSION, EXECUTE on DBMS_SQL, SELECT ANY TABLE 
REM  granted explicitly. 
REM 
REM SQL> exec coal 
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 
set serveroutput on 
 
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; 
/ 
 
REM grant execute on coal to public; 
REM drop public synonym coal; 
REM create public synonym coal for coal;