Change tablespace for INDEX/TABLE

 

1. Normal case

ALTER INDEX index_name rebuild TABLESPACE tablespace_name;

 

2. Index with partition

If TABLESPACE_NAME is null, which means the index has been partitioned.

 

SELECT TABLESPACE_NAME, a.* FROM user_indexes a;

 

if run below directly:

ALTER INDEX index_name rebuild TABLESPACE tablespace_name;

 

you will get error:

ORA-14086: a partitioned index may not be rebuilt as a whole

 

The solution is:

ALTER INDEX index_name rebuild PARTITION partition_name TABLESPACE tablespace_name;

Rebuild Index

----------------------------------------

select concat(concat('ALTER INDEX ',index_name),' REBUILD TABLESPACE tablespace_name ;') from dba_indexes where owner = ' owner' and tablespace_name is not null;

select concat(concat(concat('ALTER INDEX ',index_name),' REBUILD PARTITION '), concat(partition_name,' TABLESPACE tablespace_name;')) from DBA_IND_PARTITIONS where index_owner = 'owner';

----------------------------------------

Select all tablespace_name

----------------------------------------

select distinct tablespace_name from dba_tables where owner = '';

select distinct tablespace_name from dba_tab_partitions where table_owner = '';

select distinct tablespace_name from dba_tab_subpartitions where table_owner = '';

select distinct tablespace_name from dba_indexes where owner = '';

select distinct tablespace_name from dba_ind_partitions where index_owner = '';

select distinct tablespace_name from dba_ind_subpartitions where index_owner = '';

----------------------------------------