How to resolve “ORA-23293: Cannot rename a column which is part of a join index?”

oracleoracle-11g-r2

Disclaimer: I am a developer, not a DBA, but this question seems more appropriate for DBAs than for StackOverflow. Now to my question:

We have a table in an Oracle Exadata schema that uses a numeric, sequence-generated PK simply called ID. We would like to rename this column to [TABLENAME]_ID so that its name is consistent with its table name (and therefore consistent with other naming conventions used at the company).

When I attempt to rename this column with ALTER TABLE TABLENAME RENAME COLUMN ID TO TABLENAME_ID;, I'm slapped with the message SQL Error: ORA-23293: Cannot rename a column which is part of a join index. The obvious suggestion from Oracle is to drop the join index so that I can rename the column, but that's the fun part: I (nor any of my coworkers) have ever explicitly created such an index, nor are aware of any that exist.

At the moment, my only thought is that the index in question was auto-generated by the creation of some other object or by some unique configuration of this table. I've tried searching in user_indexes and user_objects for signs of the join index without any luck; are there other system tables I should consult to hunt this index down?

FWIW, the table has a few things about it that make it unique vs other tables where we have been able to renamed the ID column:

  • This table has a CLOB column in it.
  • This table is partitioned based on a date column. Partitions are based on the year.
  • Other tables in the schema are partitioned based on their foreign key references to this table.

Can anyone clue me in on whether there's some "shadow" join index created by one of the above features, and/or the correct way to find this phantom join index? Thanks!

Best Answer

There is no such index/object type as JOIN INDEX in the USER_INDEXES/USER_OBJECTS views. A join index is a BITMAP index, search for that. Also you can query what columns are indexed from USER_IND_COLUMNS view. Thinking backwards, you can filter to one specific column and check whether it is indexed or not (this will also reveal the index name):

select * from user_ind_columns where column_name = 'ID' and table_name = 'TABLENAME';

When an index name starts as SYS_.... and has a seemingly random identifier on the end, then that index was generated automatically, which is quite unlikely for a bitmap join index :)

You can confirm the index being a bitmap join index e.g with the use of dbms_metadata.get_ddl, which will show you the actual join condition used:

select dbms_metadata.get_ddl('INDEX', 'INDEX_NAME', 'INDEX_OWNER') from dual;
Related Question