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_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!