Thanks Don, The problem here is after splitting the table, Oracle (9i) also splits the local indexes accordingly but "moves" the indexes (originally defined from different tablespace) to the same tablespace of the underlying table. The steps I did were:
1. Create tables with single partition in one (data) tablespace
2. Create local indexes based on the single partition (above) in another (index) tablespace
3. Perform partition splitting on single partition to create multiple partitions
An illustrated example of all the above steps:
create table ETL_XCHANGE (
xchange_key NUMBER(16) NOT NULL,
partition by range (PERIOD_KEY)
subpartition by hash (ACCOUNT_KEY) subpartition template
( subpartition S01 tablespace STAGE_DATA
,subpartition S02 tablespace STAGE_DATA
,subpartition S03 tablespace STAGE_DATA
(partition ETLX_MAX values less than (MAXVALUE) tablespace STAGE_DATA);
create index XCHANGE_IDX1 on ETL_XCHANGE (XCHANGE_KEY, PERIOD_KEY, PARTITION_ID)
nologging local (partition ETLX_MAX tablespace STAGE_IDX);
alter table ETL_XCHANGE split partition ETLX_MAX at ($month18)
itno (partition ETLX_1, partition ETLX_MAX tablespace STAGE_DATA);
After splitting, Oracle automatically splits the local indexes accordingly as well but have both data and indexes in tablespace STAGE_DATA.
The Oracle 9i documentation also stated:
"Oracle splits the corresponding local index partition, even if it is marked UNUSABLE. Oracle marks UNUSABLE, and you must rebuild, the local index partitions corresponding to the split partitions. The new index partitions inherit their attributes from the partition being split. Oracle stores the new index partitions in the default tablespace of the index partition being split. If that index partition has no default tablespace, then Oracle uses the tablespace of the new underlying table partitions."
Any help will be much appreciated.