How do I find all the functional indexes on a column in Oracle

Question

Say I have a program that searches a database for columns to modify, as part of a database conversion process.

If I attempt to alter a column with a functional index defined gives the following error:

ORA-30556: functional index is defined on the column to be modified

Looking up the ORA code, the solution is to "Drop the functional index before attempting to modify the column."

Great! So how do I find all the functional indexes on that column?

The user_ind_columns view looks like a good start, but functional indexes have things like "SYS_NC00042$" in their COLUMN column. Looking around the other user_ views, I'm not seeing anything obvious. Am I missing something?

Or am I going about this the wrong way entirely?

Answer 1

The table user_ind_expressions describes expressions of function based indexes.

Oracle 11.2 link

Answer 2

Look at the INDEX_TYPE column of USER_INDEXES:

select table_name, index_name, index_type
from user_indexes
where index_type like 'FUNCTION%'
order by table_name, index_name

TABLE_NAME                      INDEX_NAME                      INDEX_TYPE                 
------------------------------  ------------------------------  ---------------------------
SOME_TABLE                      SOME_TABLE_FUNC_INDEX           FUNCTION-BASED NORMAL