tablespace move

 set feedback off

set echo off

set serverout off

set pages 0

set lines 200 

col name new_val dbname noprint

select name from v$database;

spool mvlobs_&&dbname\.sql

prompt spool mvlobs_&&dbname

select 'alter table ' || owner || '.' || table_name ||

' move lob(' || column_name || ') store as (tablespace '||tablespace_name||'_NEW);'

from dba_lobs where tablespace_name not in ('SYSTEM', 'SYSAUX', 'UNDOTBS1','USERS','TEMP')

order by tablespace_name;

prompt spool off

spool off

set feedback off

set echo off

set serverout off

set pages 0

set lines 200 

col name new_val dbname noprint

select name from v$database;

spool mvtabs_&&dbname\.sql

prompt spool mvtabs_&&dbname

select 'alter table '||owner||'.'||table_name||' move tablespace '||tablespace_name||'_NEW;' 

from dba_tables 

where tablespace_name not in ('SYSTEM', 'SYSAUX', 'UNDOTBS1','USERS','TEMP') 

and tablespace_name in (select tablespace_name from dba_tablespaces where bigfile = 'NO') 

order by tablespace_name,blocks;    

prompt spool off

spool off

set feedback off

set echo off

set serverout off

set pages 0

set lines 200 

col name new_val dbname noprint

select name from v$database;

spool rebuild_idx_&&dbname\.sql

prompt spool rebuild_idx_&&dbname

select 'alter index '||owner||'.'||table_name||' rebuild tablespace '||tablespace_name||'_NEW;' 

from dba_indexes 

where tablespace_name not in ('SYSTEM', 'SYSAUX', 'UNDOTBS1','USERS','TEMP')

and index_type != 'LOB'

and tablespace_name in (select tablespace_name from dba_tablespaces where bigfile='NO')

order by tablespace_name, table_name; 

prompt spool off

spool off