move oracle tablespace

reference to http://www.my-whiteboard.com/oracle-dba/oracle-performance-tuning-moving-table-from-one-tablespace-to-another-using-alter-database-move-command.html

1. Create a new locally managed tablespace

CREATE TEMPORARY TABLESPACE COSTPOINT_DATA4
TEMPFILE ‘K:\oradata\SIIT\COSTPOINT_DATA4_1.DBF’ SIZE 2000M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 256K;

2, Create three sql files for moving tables to new tablespace, rebuild indexes and compute statistics

connect deltek/xxx@fin;
set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\reorg\reorg_tables.sql;

select 'connect deltek/xxx@fin;' from dual;
select ‘alter table ‘||table_name||’ move tablespace COSTPOINT_DATA4;’
from user_tables where tablespace_name=’COSTPOINT_DATA’;
spool off;

spool C:\Oracle\job\reorg\rebuild_index.sql;
select ‘connect deltek/xxx@fin;’ from dual;
select ‘alter index ‘|| INDEX_NAME|| ‘ rebuild TABLESPACE costpoint_INDEX1;’ from user_indexes
where table_name in (select table_name from user_tables where tablespace_name=’COSTPOINT_DATA’);
spool off;

spool C:\Oracle\job\reorg\compute_stat.sql;
select ‘connect deltek/xxx@fin;’ from dual;
select ‘analyze table ‘|| table_name|| ‘ compute statistics;’ from user_tables where tablespace_name=’COSTPOINT_DATA’;
spool off;

exit;

3, Run this batch file:

c:
cd C:\Oracle\job\reorg
sqlplus /nolog @reorg_tables.sql
sqlplus /nolog @rebuild_index.sql
sqlplus /nolog @compute_stat.sql