如何在oracle中查找表，视图和同义词的所有索引及其列 [英] how to find all indexes and their columns for tables, views and synonyms in oracle

问题描述

我记下了以下查询，该查询将列出特定表的索引名称及其列:

I jotted down the following query which will list the index name and its columns for a particular table:

select 
b.uniqueness, a.index_name, a.table_name, a.column_name 
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name 
and a.table_name = upper('table_name')
order by a.table_name, a.index_name, a.column_position;

我想对此进行修改，以便如果我传入view或synonym也可以.我们的系统具有不同的视图，同义词，因此如果我可以为其提供名称(无论是视图同义词还是表)，它都会吐出索引及其列，这对查询非常有帮助.

I want to modify this so that if I pass in a view or synonym also it works. Our system has variations of views, synonyms so it will be really helpful to have one query to which i can just supply the name (be it view synonym or table) and it would spit out the indexes and their columns.

推荐答案

您的查询应适用于同义词以及表格.但是，您似乎希望在没有索引的视图上建立索引.也许是物化视图?

Your query should work for synonyms as well as the tables. However, you seem to expect indexes on views where there are not. Maybe is it materialized views ?

这篇关于如何在oracle中查找表，视图和同义词的所有索引及其列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！