Splitting table and drop partition of subpartition table:rebuild UNUSABLE subpartition index

Today i had nasty experience in splitting and dropping a partition table of300Gb,when i am doing split i get ORA-1652: unable to extend tablepsace of <Usertablespace>.

And we are purging the table to clean the data and space,So i don't want to add datafiles to the existing tablespace,So then i splitting the table into new tablespace to drop the old data's.

During the split it make the indexes as UNUSABLE :( that makes the application not able to process the data and insert the data.

Then the table is intermediate stat where my partitions are in non standard tablespace,So 1st i desired to move to original tablespace
then rebuild the indexes.

Spools and logs:
++ Create new tablepspace to purge the data's

create tablespace cl_message datafile '/Backup/oradata/B02/datafile/cl_message_01.dbf' size 24g;
alter tablespace cl_message add datafile '/Backup/oradata/B02/datafile/cl_message_12.dbf' size 24g;
alter tablespace cl_message add datafile '/Backup/oradata/B02/datafile/cl_message_13.dbf' size 24g;
++ Split the table to drop the old data.
ALTER TABLE CL2.CL2_DATASETS_MESSAGES SPLIT PARTITION CL2_DM_CURRENT AT (193540)
INTO ( PARTITION CL2_DM_CURR2 tablespace cl_message, PARTITION CL2_DM_CURRENT tablespace cl_message)
update global indexes;   

++ Drop partition
alter table CL2.CL2_DATASETS_MESSAGES drop partition CL2_DM_CURR2 update global indexes;
alter table CL2.CL2_DATASETS_MESSAGES drop partition CL2_DM_151585 update global indexes;

++ Now i see the data is in wrong tablespace,So i have to move to standard tablespace, During that time i get below error,So it that i have to move subpartition rather than partition.

++ How to move subpartion to other tablespace.

select 'alter table '||TABLE_OWNER||'.' || table_name || ' move subpartition ' || SUBPARTITION_NAME|| ' tablespace CL  update global indexes;'
from dba_tab_subpartitions where table_name = 'CL2_DATASETS_MESSAGES' and PARTITION_NAME = 'CL2_DM_CURRENT';
++ Update global index didnt work since already my indexes are in unusable state.

++ So used below to move the data with out update global index.
select 'alter table '||TABLE_OWNER||'.' || table_name || ' move subpartition ' || SUBPARTITION_NAME|| ' tablespace CL;'
from dba_tab_subpartitions where table_name = 'CL2_DATASETS_MESSAGES' and PARTITION_NAME = 'CL2_DM_CURRENT';

alter table CL2.CL2_DATASETS_MESSAGES move subpartition CL2_DM_CURRENT_H tablespace CL;
alter table CL2.CL2_DATASETS_MESSAGES move subpartition CL2_DM_CURRENT_G tablespace CL;
alter table CL2.CL2_DATASETS_MESSAGES move subpartition CL2_DM_CURRENT_F tablespace CL;
alter table CL2.CL2_DATASETS_MESSAGES move subpartition CL2_DM_CURRENT_E tablespace CL;
alter table CL2.CL2_DATASETS_MESSAGES move subpartition CL2_DM_CURRENT_D tablespace CL;
alter table CL2.CL2_DATASETS_MESSAGES move subpartition CL2_DM_CURRENT_C tablespace CL;
alter table CL2.CL2_DATASETS_MESSAGES move subpartition CL2_DM_CURRENT_B tablespace CL;
alter table CL2.CL2_DATASETS_MESSAGES move subpartition CL2_DM_CURRENT_A tablespace CL;

++ Then rebuild the subpartition indexes

If index status is N/A in dba_indexes then its partition index
and If status is  N/A in dba_ind_partitions then its sub partition index
Check in all_ind_subpartitions.

++ Check the status of of index of partition and sub partition.

<< http://oracletechdba.blogspot.com/2014/12/to-know-status-of-sub-partition-indexes.html >>
   
++ Rebuild Index with partition with status UNUSABLE.
Normal index status check and script to rebuild
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE' and table_name='CL2_DATASETS_MESSAGES';

Partition index status check and script to rebuild

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from dba_ind_partitions where
status='UNUSABLE' and
( index_owner, index_name ) IN  ( select owner, index_name from all_part_indexes
where owner = 'CL2'
and table_name = 'CL2_DATASETS_MESSAGES')
/
Sub Partition index status and script to rebuild
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' tablespace CL ;' from dba_ind_subpartitions
where status='UNUSABLE'
and
( index_owner, index_name ) IN  ( select owner, index_name from all_part_indexes
where owner = 'CL2'
and table_name = 'CL2_DATASETS_MESSAGES')
/

++ Rebuild the indexes then check the status.
                                         
++ Check index status of Table name,Partition name,Sub partition name,indexname,Global index or partition index,status of the index.





SELECT a.index_name, b.table_name, a.partition_name, c.index_type, c.global_stats, d.subpartition_name, d.status
FROM dba_IND_PARTITIONS a
, dba_TAB_PARTITIONS b
, dba_INDEXES c
, all_ind_subpartitions d
WHERE a.partition_name= b.partition_name
AND c.index_name=a.index_name
AND c.table_name = b.table_name
and a.partition_name= d.partition_name
AND a.index_name in (select index_name from all_part_indexes where table_name = '&table_name' )
AND b.table_name= '&table_name';