Kbase P75415: Getting error ORA-30556 in ORACLE against an index that has DESC components
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/8/2004 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.1x
FACT(s) (Environment):
Oracle DataServer
FACT(s) (Environment):
Oracle
SYMPTOM(s):
Getting error ORA-30556 when trying to perform an ALTER command within ORACLE
Getting error ORA-30556 when trying to alter a shadow column within ORACLE
Getting error ORA-30556 when trying to alter a U##column within ORACLE
Getting error ORA-30556 when trying to increase column width for a shadow column, U##column, within ORACLE
Error: ORA-30556 functional index is defined on the column to be modified
Getting no errors when trying to increase column width for a regular column, without the U##, within ORACLE
Example of alter command used;
alter table xyz modify U##zzz varchar2(8);
The index on table is descending or one or more of the components within the index are in descending order
CAUSE:
This issue is related to Oracle?s implementation of descending indices. If an index contains a descending component, Oracle creates a hidden field whose definition is the same as the actual field. This field is not part of the visual table definition and can not be altered. Therefore, if the actual index component is being changed, you must delete the index first so the hidden field will also be deleted. Then you can alter the field and recreate the index.
FIX:
1. Drop the index
2. Increase the size of the column, U##column
3. Add the index again on the table