HappyPappy Blog

This is particularly handy if you want to have multiple schemas of a big application inside the same database (for testing i.e. one schema for development and another for testing) but you’d also like the tables and indexes to reside in their own tablespaces.

This consists of two basic steps:

1) Run two generator scripts which will create the required scripts to actually move the tables and indexes; these scripts are:

set echo off      
column order_col1 noprint    
column order_col2 noprint      

set heading off    
set verify off    
set feedback off    
set echo off      
spool c:\temp\movetab.sql      

select 'alter table ' || segment_name || ' move tablespace QTSTDATA;'    
from   user_segments 
where  segment_type in ('TABLE'); 

spool off      
set heading on    
set verify on    
set feedback on    
set echo on 

To create a table moving script (replace TABLESPACENAME with the tablespace you want and replace c:\temp with the folder into which you’d like to store the generated script.

set echo off      
column order_col1 noprint    
column order_col2 noprint      

set heading off    
set verify off    
set feedback off    
set echo off      
spool c:\temp\moveindx.sql      

select 'alter index ' || segment_name || ' rebuild tablespace QTSTINDX;'    
from   user_segments 
where  segment_type in ('INDEX'); 

spool off      
set heading on    
set verify on    
set feedback on    
set echo on 

To generate the script to move the indexes, again change the tablespace and folder names as required.

2) Run the generated scripts to do the actual moves….either load into your favourite SQL tool or in sqlplus this would be executed by running @c:\temp\movetabs and @c:\temp\moveindx repsectively.

Voila you’re done, your tables and indexes now live in their own tablespaces.