Posted by For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes.
SQL> create table blah ( x varchar2(30));
Table created.
SQL> create index blah_ix on blah ( upper(x));
Index created.
SQL> select column_name from user_ind_columns
2 where index_name = 'BLAH_IX'
3 /
COLUMN_NAME
------------------------------
SYS_NC00002$
Don’t panic. Simply take a look at USER_IND_EXPRESSIONS to find the function that you used.
SQL> select column_expression from user_ind_expressions
2 where index_name = 'BLAH_IX'
3 /
COLUMN_EXPRESSION
--------------------------------------------------------------------------------
UPPER("X")
Easy peasy
Learning is not a spectator sport 
Also the same info available in user_tab_cols.
demo@ORA12C> create table t as select * from all_objects;
Table created.
demo@ORA12C> create index t_idx on t( owner, lower(object_type), upper(object_name) );
Index created.
demo@ORA12C> column column_name format a15
demo@ORA12C> column data_default format a20
demo@ORA12C> select column_name, column_position
2 from user_ind_columns
3 where table_name =’T’
4 and index_name =’T_IDX’;
COLUMN_NAME COLUMN_POSITION
————— —————
OWNER 1
SYS_NC00027$ 2
SYS_NC00028$ 3
demo@ORA12C> select column_name, data_default
2 from user_tab_cols
3 where table_name =’T’
4 and column_name in (‘SYS_NC00027$’,’SYS_NC00028$’);
COLUMN_NAME DATA_DEFAULT
————— ——————–
SYS_NC00027$ LOWER(“OBJECT_TYPE”)
SYS_NC00028$ UPPER(“OBJECT_NAME”)
demo@ORA12C>