How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle

I will explain How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle in Oracle.

 

Move Tables Oracle

Especially you want to drop any tablespace so you need to discharge or move all tables to the new tablespace.

 

 

 

You can move any table to new tablespace in Oracle with following command.

ALTER TABLE MEHMET.SALIH MOVE TABLESPACE NEW_TABLESPACE_NAME;

 

You can move lots of tables to the new tablespace with using generate move scripts.

select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'NEW_TBS_NAME;' from dba_tables where tablespace_name='PMDB_DAT1';

 

Above SQL script generated following move scripts.

ALTER TABLE ADMUSER.USEROBS move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.UMEASURE move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.UACCESS move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.UDFTYPE move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.ACTVTYPE move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.ACTVCODE move tablespace NEW_TBS_NAME;

 

If you execute above scripts , tables will be moved to new tablespace.

 

If your tables are very big, you can use parallel option and move tables parallel like following.

select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'new_tablespace_name parallel 8;' from dba_tables where tablespace_name='DATA';

 

Use above sql query result and execute them to move tables from one tablespace to new tablespace.

 

If your tables are very big, you can use PARALLEL and ONLINE option and move tables parallel and ONLINE like following.

select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'new_tablespace_name parallel 8;' from dba_tables where tablespace_name='DATA';

 

Above SQL script generates table move script as follows. You can use the following script to move tables.

ALTER TABLE MEHMETSALIH.TEST_SUM_COUNTRY_T1 move tablespace DEFAULT_TBS parallel 8 ONLINE;
ALTER TABLE MEHMETSALIH.TEST_SUM_COUNTRY_T2 move tablespace DEFAULT_TBS parallel 8 ONLINE;
ALTER TABLE MEHMETSALIH.TEST_SUM_DEVICE_FULL_INFO_03092019 move tablespace DEFAULT_TBS parallel 8 ONLINE;
ALTER TABLE MEHMETSALIH.TEST_SUBS_PACK_HIST move tablespace DEFAULT_TBS parallel 8 ONLINE;

 

Move Tables to New Tablespace

Some tables are partitioned, so if the tables are partitioned, then you need to move both partitions and subpartitions also.

SQL> ALTER TABLE TEST_TABLE MOVE PARTITION PART_2020 ONLINE TABLESPACE NEW_TABLESPACE_NAME UPDATE INDEXES;

Table altered.

SQL>

SQL> ALTER TABLE TEST_TABLE MOVE SUBPARTITION SYS_SUBP1453 ONLINE TABLESPACE users UPDATE INDEXES;

Table altered.

 

Move Partitions Oracle

 

You can generate the move all partitions script as follows.

select 'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE NEW_TABLESPACE_NAME UPDATE INDEXES;' from dba_tab_partitions where TABLESPACE_NAME = 'OLD_TABLESPACE_NAME';

 

 

You can generate the move all subpartitions script as follows.

SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME ||' TABLESPACE NEW_TABLESPACE_NAME parallel 64 UPDATE INDEXES;' FROM DBA_TAB_SUBPARTITIONS where TABLESPACE_NAME = 'OLD_TABLESPACE_NAME';

 

If you got the following error during moving partition, then you need to move subpartitions of that partition firstly.

SQL> ALTER TABLE TEST_TABLE MOVE PARTITION PART_2020 TABLESPACE NEW_TABLESPACE_NAME PARALLEL(DEGREE 64) NOLOGGING;
*
ERROR at line 1:
ORA-14257: cannot move a partition which is a composite partition



SQL> ALTER TABLE TEST_TABLE MOVE SUBPARTITION SYS_SUBP1453 ONLINE TABLESPACE users UPDATE INDEXES;

Table altered.

 

 

Then modify default tablespace attributes of the partitions as follows.

ALTER TABLE TEST_TABLE MODIFY DEFAULT ATTRIBUTES FOR PARTITION PART_2020 TABLESPACE NEW_TABLESPACE_NAME;

 

You can generate script of this modifying default tablespace attributes of the partitions as follows.

 SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MODIFY DEFAULT ATTRIBUTES FOR PARTITION ' ||PARTITION_NAME ||' TABLESPACE NEW_TABLESPACE_NAME;' FROM DBA_tab_partitions WHERE tablespace_name='OLD_TABLESPACE_NAME';

 

 

 

Index Rebuild on New Tablespace

When you move all tables to the different tablespace, then you can start to move the indexes also.

You can use this moving operation for normal index rebuild.

 

You can move lots of indexes to the new tablespace with using generate rebuild scripts.

select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'NEW_TABLESPACE online parallel 8;' 
from dba_indexes where tablespace_name='OLD_TABLESPACE';

 

Above SQL script generates following rebuild scripts.

ALTER INDEX ADMUSER.PK_ACCOUNT REBUILD TABLESPACE new_tablespace online parallel 8;
ALTER INDEX ADMUSER.NDX_ACCOUNT_PARENT_ACCT REBUILD TABLESPACE new_tablespace online parallel 8;
ALTER INDEX ADMUSER.NDX_ACCOUNT_DSI REBUILD TABLESPACE new_tablespace online parallel 8;
ALTER INDEX ADMUSER.NDX_ACCOUNT_UPD REBUILD TABLESPACE new_tablespace online parallel 8;

 

 

Rebuild Index Partition generate script is as follows.

 

set linesize 158;
select 
'alter index '||owner||'.'||segment_name||' rebuild partition '|| partition_name||' tablespace NEW_TABLESPACE;'
from dba_segments where tablespace_name='OLD_TABLESPACE' and segment_type='INDEX PARTITION';

You can run the above script result to rebuild index  partitions.

 

Rebuild Index Subpartition generate script is as follows.

set linesize 158;
select 'alter index '||owner||'.'||segment_name||
' rebuild subpartition '|| partition_name||' tablespace NEW_TABLESPACE online;'
from dba_segments where tablespace_name='OLD_TABLESPACE' and segment_type='INDEX SUBPARTITION';

You can run the above script result to rebuild index subpartitions.

 

 

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )