Moving Objects To Another Tablespace

We had some tablespaces that were full and I moved some tables to another tablespace and thought it would be neat to document it here. Moving objects between tablespaces can be useful whenever you need to reorganize tables. We've seen where moving objects from one tablespace to another and then back again helps keep the number of extents down. Also, whenever a power user puts data in a tablespace they shouldn't have, then I can move it back to where it should be. If you decide to do some tablespace consolidatation, reorganization or other changes, moving the objects is also useful. Beware there are some limitations to the alter table move tablespace mainly around data types. Please consult the Oracle documentation. I will document some SQL examples below moving a table, an index, a partition, and a sub-partition. If you need to move lots of objects, then you will want to write dynamic SQL which pulls information from the DBA views.

alter table OWNER.TABLE_NAME move tablespace NEW_TBSP parallel 8 nologging;

alter index OWNER.INDEX_NAME rebuild tablespace NEW_TBSP online;

alter table OWNER.TABLE_NAME move partition XX tablespace NEW_TBSP parallel 16;

alter table OWNER.TABLE_NAME move subpartition SUB_PART_NAME tablespace NEW_TBSP parallel 8;

Note: The degree of parallelism above indicates the degree used to do the move not the default degree of parallelism for the object.