Moving tables and indexes

from http://www.oracle-scripts.net/moving-tables-and-indexes/

REM ################################################
REM # Creator: Vincent Fenoll
REM # Created: 2010/01/22
REM # Name: Procedure Move tables and indexes
REM  ################################################
REM #
REM # Compatible: Oracle 9i 10g 11g
REM#
REM ################################################
REM #
REM # Move Tables & Indexes from « current_tablespace » to « new_tablespace » for the « myschema » user
REM # Change/Replace: myschema, new_tablespace, current_tablespace
REM #
REM ################################################

sqlplus « / as sysdba »

REM *********************************************************************
REM MOVES
REM *********************************************************************

REM Moving tables and indexes
set head off feed off echo off pages 2000 lines 150
spool move.sql

REM Tables

--alter table myschema.table_name move tablespace new_tablespace;
select ‘ALTER TABLE myschema.’ || table_name || ‘ move tablespace new_tablespace;’ from dba_tables where owner=’myschema’ and tablespace_name=’current_tablespace’;

REM Indexes

--alter index myschema.index_name rebuild tablespace new_tablespace;
select ‘ALTER INDEX myschema.’ || index_name || ‘ rebuild tablespace new_tablespace online;’ from dba_indexes where owner=’myschema’ and index_type<>’LOB’ and tablespace_name=’current_tablespace’;

REM LOB Data & indexes

--alter table myschema.table_name move lob(column_name) store as segment_name tablespace new_tablespace;
select ‘ALTER TABLE myschema.’ || table_name || ‘ MOVE LOB(‘ || column_name || ‘)
STORE AS ‘ || segment_name || ‘ (TABLESPACE new_tablespace);’from dba_lobs where index_name like ‘SYS_IL%$$’ and owner=’myschema’ and tablespace_name=’current_tablespace’;

REM  Rebuild of UNUSABLE indexes

--alter index myschema.index_name rebuild online
select ‘ALTER INDEX myschema.’ || index_name || ‘ rebuild online;’ from dba_indexes where owner=’myschema’ and status=’UNUSABLE’;

spool off
exit

————————————————————————-
– $vi move.sql
– Delete all extra lines
————————————————————————-

sqlplus « / as sysdba »
SQL>@move.sql

REM *********************************************************************
REM VERIFICATIONS
REM *********************************************************************

– I verify that all the tables and indexes have been moved to the new tablespace
select distinct TABLESPACE_NAME from dba_tables where owner=’myschema’;
select distinct TABLESPACE_NAME from dba_indexes where owner=’myschema’;

– I verify that all indexes are VALID
select distinct status from dba_indexes where owner=’myschema’;

REM *********************************************************************
REM UPDATE STATS
REM *********************************************************************

exec sys.dbms_stats.gather_schema_stats(‘myschema’, NULL, FALSE, ‘FOR ALL INDEXED COLUMNS’, NULL, ‘DEFAULT’, TRUE);

REM *********************************************************************
REM Grants for future objets
REM *********************************************************************

alter user myschema default tablespace new_tablespace;
alter user myschema quota unlimited on new_tablespace;