How to move tables, indexes and partitions to a different tablespace in Oracle

I had a case where I needed to move objects to a new tablespace, quite a few objects. As there was a lot of objects (tables, indexes and partitions) I decided to automate the code creation from the database metadata.

Moving Objects To A New Tablespace

The first thing to understand is how to move each type of object into a different tablespace. This is done using an ALTER statement, on each of the object types.

--------------------------------------------------------------------------------
-- Move Table to new tablespace
--------------------------------------------------------------------------------
     ALTER
     TABLE <owner>.<table_name>
      MOVE
TABLESPACE <new_tablespace>
;

After a table has been moved, the index will need rebuilding. There are two choices here;

1. Rebuild the index, leaving it where it is/was.
2. Rebuild the index, moving it at the new tablespace.

--------------------------------------------------------------------------------
-- Rebuild Index
--------------------------------------------------------------------------------
  ALTER
  INDEX <owner>.<index_name>
REBUILD
;

--------------------------------------------------------------------------------
-- Move & Rebuild Index
--------------------------------------------------------------------------------
     ALTER
     INDEX <owner>.<index_name>
   REBUILD
TABLESPACE <new_tablespace>
;

If partitions are involved, then the alter statement needs to point to the individual partitions, and (if required) change the default tablespace for new partitions.

This applies to both the table and index partitions:

--------------------------------------------------------------------------------
-- Move TABLE Partition
--------------------------------------------------------------------------------
     ALTER
     TABLE <owner>.<table_name>
 PARTITION <partition_name>
      MOVE
TABLESPACE <new_tablespace>
;

--------------------------------------------------------------------------------
-- Move INDEX partition
--------------------------------------------------------------------------------
     ALTER
     INDEX <owner>.<index_name>
 PARTITION <index_partition_name>
   REBUILD
TABLESPACE <new_tablespace>
;
--------------------------------------------------------------------------------
-- Change the default tablespace
--------------------------------------------------------------------------------
     ALTER
     INDEX <owner>.<index_name>
    MODIFY DEFAULT ATTRIBUTES
TABLESPACE <new_tablespace>
;

Create Alter Statements Using Oracle Metadata

Below is the SQL to create the above statements from the metadata within oracle.

--------------------------------------------------------------------------------
-- Create ALTER Statements
-- - Change Table/Index TABLESPACE
-- - Modify Default index partition (partitioned tables)
--------------------------------------------------------------------------------
SELECT COALESCE(t.owner, s.owner) AS owner,
     --s.owner                    AS segment_owner,
     --t.owner                    AS table_owner,
     --i.owner                    AS index_owner,
     --i.table_owner              AS index_table_owner,
       t.table_name,
       s.Segment_Name,
       s.Segment_Type,
       s.Tablespace_Name,
     --s.Bytes,
     --s.Blocks,
     --s.Extents,
       q'[ALTER ]'
       || CASE WHEN s.Segment_Type = 'INDEX PARTITION' THEN q'[INDEX]' WHEN s.Segment_Type = 'TABLE PARTITION' THEN q'[TABLE]' ELSE s.Segment_Type END --|| Segment_Type
       || q'[ ]'
       || s.owner --|| USER
       || q'[.]' || s.Segment_Name
       || CASE WHEN s.Segment_Type IN ('INDEX', 'INDEX PARTITION') THEN q'[ REBUILD ]' ELSE q'[ MOVE ]' END
       || CASE WHEN s.Segment_Type LIKE '% PARTITION' THEN q'[PARTITION ]' || s.Partition_Name || q'[ ]' ELSE q'[]' END
       || q'[TABLESPACE ]'
       || q'[<NEW_TABLESPACE>]'
       || CASE WHEN s.Segment_Type = 'INDEX PARTITION' THEN q'[ NOLOGGING]' ELSE q'[]' END
       || q'[;]'
                                                                                AS alter_statement,
       CASE WHEN s.Segment_Type = 'INDEX PARTITION' THEN q'[ALTER INDEX ]' || s.Segment_Name || q'[ MODIFY DEFAULT ATTRIBUTES TABLESPACE ]' || q'[<NEW_TABLESPACE>]' || q'[;]' ELSE NULL END
                                                                                AS default_index_partition
  FROM dba_Segments s
     LEFT JOIN all_indexes i
       ON i.index_name = s.Segment_Name
      AND i.owner      = s.owner
     LEFT JOIN all_tables t
       ON t.table_name = COALESCE(i.table_name,  s.Segment_Name)
      AND t.owner      = COALESCE(i.table_owner, s.owner)
 WHERE 1=1
--AND s.tablespace_name <> COALESCE(t.tablespace_name, '') -- Test for indexes which still sit in a different location (need to be moved)
--AND COALESCE(t.owner, s.owner) = '<OWNER>'
--AND t.table_name   = '<TABLE_NAME>'
ORDER BY t.table_name,
       s.Segment_Name
;

Fixing Indexes

When doing movements like this, there can be indexes left behind. Below is the SQL to search for broken indexes, and use metadata to create the required rebuild statements.

--------------------------------------------------------------------------------
-- See Broken Indexes
--------------------------------------------------------------------------------
SELECT *
  FROM all_indexes i
 WHERE 1=1
   AND i.status NOT IN ('VALID', 'N/A')
;

--------------------------------------------------------------------------------
-- Rebuild (with move) Broken Indexes
--------------------------------------------------------------------------------
SELECT i.owner,
     --s.owner             AS segment_owner,
     --t.owner             AS table_owner,
     --i.owner             AS index_owner,
     --i.table_owner       AS index_table_owner,
       t.table_name,
     --s.Bytes, s.Blocks, s.Extents,
       q'[ALTER ]'
       || q'[INDEX]'
       || q'[ ]'
       || i.owner --|| USER
       || q'[.]' || i.index_name
       || q'[ REBUILD ]'
       || q'[TABLESPACE ]'
       || q'[<NEW_TABLESPACE>]'
       || q'[;]'           AS alter_statement
  FROM all_indexes i
     LEFT JOIN all_tables t
       ON t.table_name = i.table_name
      AND t.owner      = i.table_owner
 WHERE 1=1
   AND i.status NOT IN ('VALID', 'N/A')
   AND i.owner = '<OWNER>'
ORDER BY t.table_name,
       i.index_name
;