move tablespace

select 'alter table mtsuser.'||table_name || ' move tablespace mtsuser_tab_data ;'

from dba_tables

where owner='MTSUSER'

select 'alter index mtsuser.'||index_name || ' rebuild tablespace mtsuser_IND_DATA ;'

from dba_indexes

where table_owner='MTSUSER'

set linesize 500

SELECT 'ALTER TABLE mtsuser.'||table_name ||' move lob('||column_name||') store as (tablespace mtsuser_tab_data );' FROM DBA_LOBS

WHERE owner='MTSUSER'

select 'alter table mtsuser.'||TABLE_NAME ||' move partition '||PARTITION_NAME||' tablespace mtsuser_data;' from dba_tab_partitions

where table_owner='MTSUSER'

select 'ALTER INDEX mtsuser.'||index_name ||' rebuild partition ' || partition_name ||' tablespace tnarchive_index ;'

from dba_ind_partitions

where index_owner='MTSUSER'

select 'alter table mtsuser.'||table_name ||' move partition '||partition_name||' tablespace mtsuser_data lob('||column_name ||') store as

(tablespace mtsuser_lob_data);'

from dba_lob_partitions

where table_owner='MTSUSER'