Home > oracle > Oracle: alter table x add partition causes ORA-00959

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

 

Advertisements
Categories: oracle Tags:
  1. Fittipaldi
    June 29, 2016 at 18:48

    Thanks!

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: