Move Database objects from one Tablespace to other

set echo off
set heading off
set verify off
set feedback off
set echo off

spool /d01/oracle/proddb/APPS_MOVE.sql

 

select 'alter table APPS.'||table_name||' move tablespace apps_data_test;' from DBA_tableS where OWNER='APPS';

select 'alter index APPS.'||INDEX_name||' rebuild tablespace APPS_DATA_TEST_IND;' from DBA_INDEXES where OWNER='APPS';

SELECT 'alter table APPS.'||table_name||' MOVE LOB('||column_name||') store as (tablespace APPS_DATA_TEST);' from dba_lobs where owner ='APPS' ;

SELECT 'alter table APPS.'||TABLE_NAME||' move partition '|| PARTITION_NAME||' tablespace APPS_TEST_DATA;' from dba_tab_partitions where table_owner ='APPS';

SELECT 'alter index APPS.'||index_owner||'.'||index_name||' rebuild partition '||partition_NAME||' TABLESPACE Apps_data_test_ind;' from dba_ind_partitions where index_owner ='APPS';

select 'alter TABLE APPS.'||TABLE_name||' MOVE tablespace APPS_DATA_TEST;' from DBA_INDEXES where OWNER='APPS';

 

SELECT 'alter table APPS.'||tABLE_NAME||' move partition '||partition_NAME||' lob('||COLUMN_NAME||') store as (tablespace APPS_DATA_TEST);' from dba_lob_partitions where table_owner='APPS';

 

select 'ALTER  MATERIALIZED VIEW apps.'||MView_name||' MOVE TABLESPACE APPS_DATA_test_ind;' from dba_mviews where owner='APPS';

set heading on
set verify on
set feedback on
set echo on

REM UNCOMMENT TO AUTO RUN the generated commands
REM ELSE edit APPS_MOVE.sql, modify as needed and run it
REM @tmp