如何在 Oracle 中的列上找到所有功能索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1903573/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I find all the functional indexes on a column in Oracle
提问by Darcy Casselman
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."
查找ORA代码，解决方法是“在尝试修改列之前删除功能索引”。
Great! So how do I find all the functional indexes on that column?
伟大的！那么如何找到该列上的所有功能索引呢？
The user_ind_columnsview looks like a good start, but functional indexes have things like "SYS_NC00042$" in their COLUMNcolumn. Looking around the other user_views, I'm not seeing anything obvious. Am I missing something?
该user_ind_columns视图看起来是一个好的开始，但功能索引在其COLUMN列中包含诸如“SYS_NC00042$”之类的内容。环顾其他user_视图，我没有看到任何明显的东西。我错过了什么吗？
Or am I going about this the wrong way entirely?
还是我完全以错误的方式解决这个问题？
回答by Khb
The table user_ind_expressionsdescribes expressions of function based indexes.
该表user_ind_expressions描述了基于函数的索引的表达式。
回答by Mike
Look at the INDEX_TYPEcolumn of USER_INDEXES:
看一INDEX_TYPE栏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
