SQL> CREATE TABLE TEST2 (A NUMBER(9,0), B VARCHAR2(20) not null);
Table created.
SQL> CREATE INDEX test2index1 ON test2(UPPER(B));
Index created.
SQL> alter table TEST2 modify B VARCHAR2(24);
alter table TEST2 modify B VARCHAR2(24)
*
ERROR at line 1:
ORA-30556: functional index is defined on the column to be modified
SQL> select * from user_ind_columns where table_name='TEST2';
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------
TEST2INDEX1 TEST2 SYS_NC00003$
SQL> drop index test2index1;
Index dropped.
SQL> alter table TEST2 modify B VARCHAR2(24);
Table altered.
SQL> CREATE INDEX test2index1 ON test2(UPPER(B));
Index created.
Table created.
SQL> CREATE INDEX test2index1 ON test2(UPPER(B));
Index created.
SQL> alter table TEST2 modify B VARCHAR2(24);
alter table TEST2 modify B VARCHAR2(24)
*
ERROR at line 1:
ORA-30556: functional index is defined on the column to be modified
SQL> select * from user_ind_columns where table_name='TEST2';
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------
TEST2INDEX1 TEST2 SYS_NC00003$
SQL> drop index test2index1;
Index dropped.
SQL> alter table TEST2 modify B VARCHAR2(24);
Table altered.
SQL> CREATE INDEX test2index1 ON test2(UPPER(B));
Index created.