Show columns in all indexes given the table name

column indexName    format a30  heading 'Index Name'
column columnName   format a30  heading 'Column Name'
column indexType    format a12  heading 'Index|Type'

set linesize 80
set verify off

accept tableName prompt "Enter Table Name: "

select  DBA_IND_COLUMNS.index_name  indexName,
        DBA_IND_COLUMNS.column_name columnName,
        DBA_INDEXES.index_type      indexType
from    DBA_IND_COLUMNS,
        DBA_INDEXES
where   DBA_INDEXES.table_name = '&tableName'
  and   DBA_INDEXES.table_name = DBA_IND_COLUMNS.table_name;

Example:

                                                              Index
Index Name                     Column Name                    Type
------------------------------ ------------------------------ ------------
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    NORMAL
PRODUCTS_PK                    PROD_ID                        NORMAL
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               NORMAL
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  NORMAL
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    NORMAL
PRODUCTS_PK                    PROD_ID                        NORMAL
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               NORMAL
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  NORMAL
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    NORMAL
PRODUCTS_PK                    PROD_ID                        NORMAL
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               NORMAL
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  NORMAL
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    BITMAP
PRODUCTS_PK                    PROD_ID                        BITMAP
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               BITMAP
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  BITMAP