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;
- Rebuild the index, leaving it where it is/was.
- 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 ;