Move Tables and Indexes to a new Tablespace

==========================================

Move Tables and Indexes to a new Tablespace by Example

==========================================

PURGE RECYCLEBIN;

PURGE RECYCLEBIN;

--Create New Tablespace

CREATE TABLESPACE NEW_TABLE_TBS DATAFILE '/oracle_db/db1/db_igt/new_tab_tbs.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 30000M;

CREATE TABLESPACE NEW_INDEX_TBS DATAFILE '/oracle_db/db1/db_igt/new_ind_tbs.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 30000M;

--For Non Partitioned Objects

SELECT 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE NEW_TABLE_TBS;' 

  FROM DBA_TABLES 

 WHERE tablespace_name = 'OLD_TABLE';

ALTER TABLE COLLECTOR.EXT_OVMD_CTP MOVE TABLESPACE NEW_TABLE_TBS;

--For Partitioned Objects

SELECT 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE PARTITION '||PARTITION_NAME||' TABLESPACE NEW_TABLE_TBS;' 

  FROM  DBA_TAB_PARTITIONS 

 WHERE tablespace_name = 'OLD_TABLE';

ALTER TABLE COLLECTOR.OVMD_COUNTER_DATA MOVE PARTITION P_25_31 TABLESPACE OVMD_TABLE_TBS;

--For Non Partitioned Index

SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD  TABLESPACE NEW_TABLE_TBS;' 

  FROM  DBA_INDEXES 

 WHERE tablespace_name = 'OLD_TABLE';

ALTER INDEX COLLECTOR.OVMD_COUNTER_IX REBUILD TABLESPACE NEW_INDEX_TBS;

--For Partitioned Index

SELECT 'ALTER INDEX '||DBA_IND_PARTITIONS.index_owner||'.'||DBA_IND_PARTITIONS.index_name||' REBUILD PARTITION '||DBA_IND_PARTITIONS.partition_name||' TABLESPACE NEW_INDEX_TBS;'

  FROM DBA_IND_PARTITIONS       

WHERE DBA_IND_PARTITIONS.tablespace_name = 'OLD_IND_PARTITION';

ALTER INDEX COLLECTOR.OVMD_COUNTER_DATA_A2 REBUILD PARTITION P_01_06 TABLESPACE NEW_INDEX_TBS;

--Check

SELECT * FROM DBA_SEGMENTS WHERE tablespace_name = 'OLD_TABLE';

SELECT * FROM DBA_TAB_PARTITIONS WHERE tablespace_name = 'OLD_TABLE';

SELECT * FROM DBA_IND_PARTITIONS WHERE tablespace_name = 'OLD_TABLE';

SELECT * FROM DBA_SEGMENTS WHERE tablespace_name = 'OLD_INDEX';

SELECT * FROM DBA_TAB_PARTITIONS WHERE tablespace_name = 'OLD_INDEX';

SELECT * FROM DBA_IND_PARTITIONS WHERE tablespace_name = 'OLD_INDEX';

--Drop Old Tablespace

DROP TABLESPACE OLD_TABLE INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE OLD_INDEX INCLUDING CONTENTS AND DATAFILES;