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

 

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'

;