如何查看 SQL*Plus 中所有索引（包括隐式索引）的列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3458258/
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 to see a list of all the indexes (including implicit ones) in SQL*Plus?
提问by Moeb
Is there some way to get a list of all the indexes on a particular table using SQL*Plus?
有没有办法使用 SQL*Plus 获取特定表上所有索引的列表？
I created a table
我创建了一个表
CREATE TABLE temp(
id NUMBER PRIMARY KEY,
name VARCHAR2(20));
There should be an implicit index created on the primary key (id). How can I see that index?
应该在主键 ( id)上创建一个隐式索引。我怎么能看到那个索引？
SELECT * FROM all_indexes WHERE table_name = 'temp';
gives
给
no rows selected
未选择任何行
回答by dcp
SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME = 'your_table'
SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME = 'your_table'
Note: If you want to limit the search to a specific schema, you can also do:
注意：如果要将搜索限制为特定模式，还可以执行以下操作：
SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME = 'your_table' AND OWNER = 'your_owner'
This is useful in situations where you might have the same table name in multiple schemas.
这在您可能在多个模式中具有相同表名的情况下很有用。
Also, keep in mind that Oracle stores the table names as upper case, so in your example you need to do:
另外，请记住，Oracle 将表名存储为大写，因此在您的示例中，您需要执行以下操作：
select * from all_indexes where table_name = 'TEMP';
回答by Michael Pakhantsov
SELECT * from USER_INDEXES
WHERE TABLE_NAME = UPPER('YourTableName')
回答by Amar Shah
Please write your table name in upper case
请大写您的表名
OR
或者
SELECT * FROM all_indexes WHERE lower(table_name) = 'temp';
回答by Ganesh Giri
If you want to display all indexing in a database you can try.
如果要显示数据库中的所有索引，可以尝试。
use information_schema;
SELECT * FROM statistics;
