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
Articles Related
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>;