SQL 如何在 Oracle 中获取有关索引和表所有者的信息？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7669001/
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 get information about an index and table owner in Oracle?
提问by Randy
I want to write a select statement to display the index_name, table_name, table_owner and uniqueness that exist in the data dictionary for the table user indexes. Any help would be great. My problem is I havent been able to find how to display an index_name, and table owner.
我想编写一个select语句来显示表用户索引的数据字典中存在的index_name、table_name、table_owner和唯一性。任何帮助都会很棒。我的问题是我无法找到如何显示 index_name 和表所有者。
SELECT owner, table_name FROM dba_tables;
This gives most of it.
这给了大部分。
回答by beny23
回答by Alon Adler
select index_name, column_name
from user_ind_columns
where table_name = 'NAME';
OR use this:
或者使用这个：
select TABLE_NAME, OWNER
from SYS.ALL_TABLES
order by OWNER, TABLE_NAME
And for Indexes:
对于索引：
select INDEX_NAME, TABLE_NAME, TABLE_OWNER
from SYS.ALL_INDEXES
order by TABLE_OWNER, TABLE_NAME, INDEX_NAME
回答by James Oravec
The following may help give you want you need:
以下可能有助于满足您的需求：
SELECT
index_owner, index_name, table_name, column_name, column_position
FROM DBA_IND_COLUMNS
ORDER BY
index_owner,
table_name,
index_name,
column_position
;
For my use case, I wanted the column_names and order that they are in the indices (so that I could recreate them in a different database engine after migrating to AWS). The following was what I used, in case it is of use to anyone else:
对于我的用例，我想要 column_names 和它们在索引中的顺序（以便我可以在迁移到 AWS 后在不同的数据库引擎中重新创建它们）。以下是我使用的，以防对其他人有用：
SELECT
index_name, table_name, column_name, column_position
FROM DBA_IND_COLUMNS
WHERE
INDEX_OWNER = 'FOO'
AND TABLE_NAME NOT LIKE '%$%'
ORDER BY
table_name,
index_name,
column_position
;
回答by santosh tiwary
Below are two simple query using which you can check index created on a table in Oracle.
下面是两个简单的查询，您可以使用它们检查在 Oracle 中的表上创建的索引。
select index_name
from dba_indexes
where table_name='&TABLE_NAME'
and owner='&TABLE_OWNER';
select index_name
from user_indexes
where table_name='&TABLE_NAME';
Please check for more details and index size below. Index on a table and its size in Oracle
请在下面查看更多详细信息和索引大小。 表的索引及其在 Oracle 中的大小
