Moving tables between tablespaces

• Home
• Web
• Photos
• Writing
• Contact

From 8i onwards, it's fairly simple. First move any lob columns, then move the tables, finally rebuild the indexes in the new tablespace. this example moves tables owned by WWAV from the ENKE tablespace to the WWAV tablespace

Create a file called move-tables.sql containing this:

select 'alter table ' || dtc.table_name || ' ' ||
       'move lob(' || dtc.column_name || ') ' ||
       'store as (tablespace wwav);'
  from dba_tab_columns dtc,
       dba_tables dt
 where  dtc.owner = 'WWAV'
 and dt.owner = 'WWAV'
 and dt.tablespace_name = 'ENKE'
 and dtc.data_type like '%LOB'
 and dtc.table_name = dt.table_name
/
select 'ALTER TABLE '||table_name||' MOVE TABLESPACE WWAV;'
from dba_tables
where owner = 'WWAV'
and tablespace_name = 'ENKE'
/
select 'ALTER INDEX '||index_name||' rebuild tablespace wwav;'
from dba_indexes
where owner = 'WWAV'
and tablespace_name = 'ENKE'
/

Now do this:

$ sqlplus system/manager@sid

SQL> set linesize 100
SQL> set pagesize 0
SQL> set echo off
SQL> set feedback off
SQL> set verify off
SQL> set term off
SQL> set heading off
SQL> set serveroutput off
SQL> spool move-tables
SQL> @move-tables
SQL> spool off
SQL> quit

Now run the script as the user:

$ sqlplus wwav/wwav@sid < move-tables.lst > errors
$ more errors

~MJH