Oracle Partition - Tablespace (Table Move|Index Rebuild)

About

When a table is partitioned, the physical logical entity (the segment) is no more on a 'table segment' but:

  • on 'table partition' segment
  • or 'table subpartition' segment

The tablespace information is then are on the partitions or sub-partitions, no more on the table.

The same happens for the index.

Example:

CREATE TABLE TABLE1 
(
  COLUMN1 VARCHAR2(20) 
) 
TABLESPACE USERS 
PARTITION BY RANGE (COLUMN1) 
(
  PARTITION PARTITION1 VALUES LESS THAN (100)  
);

select TABLESPACE_NAME from user_tables where table_name = 'TABLE1';
Null

Move

Partition / Subpartition

You move the physical logical entity: the segments.

The segment are on:

  • the partition without subpartition
alter table table_name move partition partition_name tablespace tabelspace_name;
  • or on the SUBPARTITIONS with subpartition
alter table table_name move SUBPARTITION subpartition_name tablespace tabelspace_name;

Effect on Index

When moving a partition/ subpartition:

  • The affected local index partitions become unusable UNLESS you use the options to maintain local indexes as well (10g and above)
  • ALL global indexes become unusable UNLESS you use the options to maintain global indexes (9i and above)

The options to maintain index is 'UPDATE INDEXES' in:

alter table table_name move subpartition subpartition_name tablespace tabelspace_name UPDATE INDEXES ;

Otherwise you have to rebuild them

Index

ALTER INDEX <INDEX_NAME> REBUILD TABLESPACE <TABLESPACE_NAME>;

Documentation / Reference


Powered by ComboStrap