SQL Script

Move Tables to New Tablespace

Updated: 02-Jun-2005
Version: 1.0

Description

Dynamic script to move tables to a new tablespace

Parameters

tspace - New Tablespace Name
iowner - table Owner
IName - Table Name

SQL Source

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

set verify off
set pause off
set doc off
set heading off

spool altertable.lst

rem define iowner = UPPER('&1')
rem define iname = UPPER('&2')
rem define tspace = UPPER('&3')

accept iowner prompt'Enter Table Owner : '
accept iname prompt'Enter Table Name (RETURN for all) : '
accept tspace prompt'Enter Table Tablespace Name  to Rebuild to : ' 

prompt
prompt 'Rebuilding tables for &iowner to tablespace &tspace '
prompt

set serverout on


DECLARE	

sql_stmt varchar2(254);	

l_target_tspace user_tablespaces.tablespace_name%TYPE := '&tspace';
l_table_owner all_tables.owner%TYPE := '&iowner';
l_ind_name all_tables.table_name%TYPE  := '&iname';

cursor get_ind is
   select owner,table_name from dba_tables
   where owner = UPPER(l_table_owner)
   and table_name like UPPER(l_ind_name||'%')  
   and temporary != 'Y'
   and tablespacE_name != UPPER ('&tspace')
   ;  

BEGIN

   FOR ind_rec in get_ind LOOP

      sql_stmt := 'alter table '||ind_rec.owner||'.'||ind_rec.table_name
               ||' move tablespace '||l_target_tspace;

      dbms_output.put_line(sql_stmt);
  
      EXECUTE IMMEDIATE sql_stmt;

   END LOOP;

END;
/

set heading on pages 2000
col segment_name form a30
col segment_type form a6
col tablespace_name form a12

select segment_name,segment_type,tablespace_name
from dba_segments
where segment_type = 'TABLE'
and owner  = UPPER('&iowner')
and segment_name like UPPER('&iname'||'%')
/

spool off

Return to Index of SQL Scripts

Site Map

SQL Script

Move Tables to New Tablespace

Updated: 02-Jun-2005Version: 1.0

Description

Parameters

SQL Source

Site Map

Updated: 02-Jun-2005
Version: 1.0