Oracle: alter table x add partition causes ORA-00959

We’ve rebuilt all indexes from one tablespace (APL_IDX) to another one (APL_INDEX).

Now, when we try to add a partition on a partitioned table we’re getting this error:

SQL> ALTER TABLE APL_TABLE ADD PARTITION PART201212 VALUES LESS THAN ('2012','12') TABLESPACE APL_DATA LOGGING;

*
Error at line 1:

ORA-00959: tablespace 'APL_IDX' does not exist

The cause is that the storage information of USER_PART_INDEXES it’s not being updated with the rebuild.

> select def_tablespace_name from user_part_indexes where table_name = 'APL_TABLE';

DEF_TABLESPACE_NAME
------------------------------
APL_IDX

From the documentation (http://docs.oracle.com/cd/B14117_01/server.101/b10755/statviews_1112.htm#sthref1314):

• DEF_TABLESPACE_NAME: For a local index, the default tablespace to be used when adding or splitting a table partition

To update all references to the old tablespace, you must modify the default attributes of the partitioned indexes:

SQL> select 'alter index ' || INDEX_NAME || ' modify default attributes tablespace APL_INDEX;' FROM user_indexes WHERE PARTITIONED ='YES' ;