SQL Script

Coalesce Tablespaces

Updated: 18-Feb-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 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 coalesce (not just one)

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

/* Coalease free space in ORacle

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  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; 
/

Return to Index of SQL Scripts

Site Map

SQL Script

Coalesce Tablespaces

Updated: 18-Feb-1999Version: 1.0

Description

Parameters

SQL Source

Site Map

Updated: 18-Feb-1999
Version: 1.0