oracle指定索引owner,如何獲得關於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

34

According to the docs, you can just do:

根據醫生的說法，你只需要:

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from USER_INDEXES

or

或

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from ALL_INDEXES

if you want all indexes...

如果你想要所有的索引…

#2

7

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

0

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

;