How do I get information about an index and table owner in Oracle

Question

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 owner  table name   FROM dba tables    This gives most of it

User · Accepted Answer

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

User · Answer

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    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

User · Answer

The following helped me as I didn t have DBA access and also wanted the column names   See  https   dataedo com kb query oracle list-table-indexes  select ind table owner           ind table name as  TABLE          ind index name         LISTAGG ind col column name                   WITHIN GROUP order by ind col column position  as columns         ind index type         ind uniqueness from sys all indexes ind join sys all ind columns ind col            on ind owner   ind col index owner            and ind index name   ind col index name where ind table owner not in   ANONYMOUS   CTXSYS   DBSNMP   EXFSYS           MDSYS    MGMT VIEW   OLAPSYS   OWBSYS   ORDPLUGINS    ORDSYS           SI INFORMTN SCHEMA   SYS   SYSMAN   SYSTEM    TSMSYS   WK TEST           WKPROXY   WMSYS   XDB   APEX 040000   APEX 040200           DIP    FLOWS 30000   FLOWS FILES   MDDATA    ORACLE OCM    XS NULL           SPATIAL CSW ADMIN USR    SPATIAL WFS ADMIN USR    PUBLIC           LBACSYS    OUTLN    WKSYS    APEX PUBLIC USER       -- AND ind table name  TableNameGoesHereIfYouWantASpecificTable  group by ind table owner           ind table name           ind index name           ind index type           ind uniqueness  order by ind table owner           ind table name

User · Answer

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

User · Answer

Below are two simple query using which you can check index created on a table in Oracle   select index name   from dba indexes  where table name   amp TABLE NAME     and owner   amp TABLE OWNER     select index name    from user indexes   where table name   amp TABLE NAME     Please check for more details and index size below  Index on a table and its size in Oracle

[sql] How do I get information about an index and table owner in Oracle?

Examples related to sql

Examples related to oracle