SQL Script

Coalesce Tablespaces

Updated : 18-February-1999
Version : 1.0

Description

In Oracle version 7.3 and above coalescing a tablespace is very easy - there is a command to do it

ALTER TABLESPACE <tablespace_name> COALESCE;

However, what if you running a 7.0, 7.1, 7,2 database (there are still many companies quite happily running these versions!) how can you quickly coalesce a tablespace ? Below is a script to do it for you. The main cursor retreives all tablespaces where there is extents to coalease (not just one)

Parameters

None.

SQL Source

spool coalesce.lst

REM Oracle 7.1 and 7.2 Only :
REM ~~~~~~~~~~~~~~~~~~~~~~~~~
REM
REM Example Output
REM ~~~~~~~~~~~~~~
REM  Tablespace DATA_1 -- coalesced 2 extents. 
REM  Tablespace DATA_2 -- coalesced 4 extents. 
REM

set serveroutput on

DECLARE

dummy integer; 
cur integer;

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

BEGIN 
  FOR ts_rec IN get_ts LOOP 

        cur:= dbms_sql.open_cursor; 
        dbms_sql.parse(cur, 
           'alter session set events'||chr(39)||
           'immediate trace name coalesce level '||ts_rec.ts#||chr(39), 
                             dbms_sql.v7); 
        dummy:= dbms_sql.execute(cur); 
        dbms_sql.close_cursor(cur); 
        dbms_output.put_line('Tablespace '||ts_rec.name||
                     ' -- coalesced     '||ts_rec.cnt||' extents.'); 

  END LOOP; 
END; 
/

spool off