db:oracle:partition:tablespace

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

See: Oracle Segment - (Table Move|Index Rebuild)

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

Table of Contents

Oracle Partition - Tablespace (Table Move|Index Rebuild)