SQL Script

Coalesce Tablespaces

Updated: 26-Sep-2002
Version: 2..0

Description

Coalesces All tablespaces within a database.

Parameters

None

SQL Source

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


set feedback off
set verify off
set serveroutput on size 20000
REM set timing on

declare
	l_sql varchar2(254);
	w_cursor_id integer;
	l_result integer;

cursor get_ts is
    select *
    from dba_tablespaces
    where status='ONLINE'and contents not in ('TEMPORARY','UNDO') ;
    
ts_rec get_ts%ROWTYPE;

begin
   w_cursor_id:=dbms_sql.open_cursor;

   FOR ts_rec IN get_ts LOOP
      l_sql := 'alter tablespace '||ts_rec.tablespace_name||' coalesce' ;
      dbms_output.put_line(l_sql);
      dbms_sql.parse(w_cursor_id,l_sql,1);
      l_result := dbms_sql.execute(w_cursor_id);
   END LOOP;
   dbms_sql.close_cursor(w_cursor_id);

dbms_output.put_line('Table Coalesce Completed.') ;

end;
/

Site Map

SQL Script

Coalesce Tablespaces

Updated: 26-Sep-2002Version: 2..0

Description

Parameters

SQL Source

Related Links

Site Map

Updated: 26-Sep-2002
Version: 2..0