如何获得关于Oracle中的索引和表所有者的信息?

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.

大部分都是这样。

3 个解决方案

#1

According to the docs, you can just do:

根据医生的说法，你只需要:

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from USER_INDEXES

或

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from ALL_INDEXES

if you want all indexes...

如果你想要所有的索引…

#2

 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

#3

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
    ;

#1