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>
      MOVE
 PARTITION <partition_name>
TABLESPACE <new_tablespace>
;

--------------------------------------------------------------------------------
-- Move INDEX partition
--------------------------------------------------------------------------------
     ALTER
     INDEX <owner>.<index_name>
   REBUILD
 PARTITION <index_partition_name>
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 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.Tablespace_Name,
       s.Partition_Name,
       s.Segment_Type,
     --s.Bytes,
     --s.Blocks,
     --s.Extents,
       CAST(s.bytes / 1024/1024      AS NUMBER(20,2)) "MB",
       CAST(s.bytes / 1024/1024/1024 AS NUMBER(6,2))  "GB",
       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 = 'TABLE PARTITION' THEN q'[ALTER TABLE ]' || s.Segment_Name || q'[ MODIFY DEFAULT ATTRIBUTES TABLESPACE ]' || q'[<NEW_TABLESPACE>]' || q'[;]' ELSE NULL END AS default_table_partition,
       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, '')
 --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
;

--------------------------------------------------------------------------------
-- Find Indexes to rebuild (at different levels)
--
-- Stolen from:
-- https://www.orafaq.com/wiki/Unusable_indexes
--------------------------------------------------------------------------------
-- Whole indexes (all table)
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '|| tablespace_name ||';' sql_to_rebuild_index,
       i.*
  FROM dba_indexes i
 WHERE i.status = 'UNUSABLE'
;

-- Indexes in partitions
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' sql_to_rebuild_index,
       i.*
  FROM dba_ind_partitions i
 WHERE i.status       = 'UNUSABLE'
;

-- Indexes with subpartitions (we have none for now)
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild subpartition '||SUBPARTITION_NAME||' TABLESPACE '||tablespace_name ||';' sql_to_rebuild_index,
       i.*
  FROM dba_ind_subpartitions i
 WHERE status = 'UNUSABLE'
;