How do I get information about an index and table owner in 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 ow开发者_JAVA百科ner, table_name   FROM dba_tables;

This gives most of it.

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

 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

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
    ;

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='&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

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;

I'll duplicate my answer from similar question Oracle - How to obtain information on index fields etc

Script which displays table, index and comma separated list of index columns

set pagesize 50000
set linesize 32000
col table_name format a30
col index_name format a30
col primary_key_name format a30
col uniqueness format a10
col columns format a200

select ui.table_name,
       ui.index_name,
       uc.constraint_name as primary_key_name,
       ui.uniqueness,
       listagg(aic.column_name, ', ') within group (order by aic.column_position) as columns
from user_indexes ui
inner join all_ind_columns aic on aic.index_name = ui.index_name
left outer join user_constraints uc on uc.index_name = ui.index_name and uc.constraint_type = 'P'
group by ui.table_name, ui.index_name, ui.uniqueness, uc.constraint_name
order by table_name;

Result:

TABLE_NAME                     INDEX_NAME                     PRIMARY_KEY_NAME               UNIQUENESS COLUMNS                                 
------------------------------ ------------------------------ ------------------------------ ---------- ----------------------------------------
ACTIVEMQ_ACKS                  ACTIVEMQ_ACKS_PK               ACTIVEMQ_ACKS_PK               UNIQUE     CONTAINER, CLIENT_ID, SUB_NAME, PRIORITY
ACTIVEMQ_ACKS                  ACTIVEMQ_ACKS_XIDX                                            NONUNIQUE  XID                                     
ACTIVEMQ_CONNECTION            ACTIVEMQ_CONNECTION_NAME_UK                                   UNIQUE     NAME                                    
ACTIVEMQ_CONNECTION            ACTIVEMQ_CONNECTION_PK         ACTIVEMQ_CONNECTION_PK         UNIQUE     ID                                      
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_CIDX                                            NONUNIQUE  CONTAINER                               
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_EIDX                                            NONUNIQUE  EXPIRATION                              
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_MIDX                                            NONUNIQUE  MSGID_PROD, MSGID_SEQ                   
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_PIDX                                            NONUNIQUE  PRIORITY                                
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_PK               ACTIVEMQ_MSGS_PK               UNIQUE     ID                                      
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_XIDX                                            NONUNIQUE  XID                                     
ACT_EVT_LOG                    SYS_C00444651                  SYS_C00444651                  UNIQUE     LOG_NR_                                 
ACT_GE_BYTEARRAY               ACT_IDX_BYTEAR_DEPL                                           NONUNIQUE  DEPLOYMENT_ID_                          
ACT_GE_BYTEARRAY               SYS_C00444634                  SYS_C00444634                  UNIQUE     ID_                                     
ACT_GE_PROPERTY                SYS_C00444632                  SYS_C00444632                  UNIQUE     NAME_                                   
ACT_HI_ACTINST                 ACT_IDX_HI_ACT_INST_END                                       NONUNIQUE  END_TIME_                               
ACT_HI_ACTINST                 ACT_IDX_HI_ACT_INST_EXEC                                      NONUNIQUE  EXECUTION_ID_, ACT_ID_                  
ACT_HI_ACTINST                 ACT_IDX_HI_ACT_INST_PROCINST                                  NONUNIQUE  PROC_INST_ID_, ACT_ID_