List indexes on the table in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-10

Table of Contents:


    Query below lists:

    (A) all indexes, along with their columns, on a table (accessible to the current user) in Oracle database

    (B) all indexes, along with their columns, on a table in Oracle database

    Query was executed under the Oracle12c Database version.

    Query

    A. Tables accessible to the current user

    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')
    group by ind.table_owner,
             ind.table_name,
             ind.index_name,
             ind.index_type,
             ind.uniqueness 
    order by ind.table_owner,
             ind.table_name;
    

    B. If you have privilege on dba_indexes and dba_ind_columns

    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.dba_indexes ind
    join sys.dba_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')
    group by ind.table_owner,
             ind.table_name,
             ind.index_name,
             ind.index_type,
             ind.uniqueness 
    order by ind.table_owner,
             ind.table_name;
    

    Columns

    • table - name of table index is defined for
    • index_name - index name
    • columns - list of index columns separated with ","
    • index_type - index type. Possible values:
      • NORMAL
      • BITMAP
      • FUNCTION-BASED NORMAL
      • FUNCTION-BASED BITMAP
      • DOMAIN
    • uniqueness - column indicating whether the index is unique or not

    Rows

    • One row represents one index
    • Scope of rows:(A) all indexes, along with their columns, on a table (accessible to the current user) in Oracle database, (B) all indexes, along with their columns, on a table in Oracle database
    • Ordered by schema name and table name

    Sample results

    Here is a view of table's indexes in Oracle SQL Developer:

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept