How to show indexes for a table in Oracle

Give a schema and a table name (or ALL) to see every index on it. Includes owner, tablespace, num_rows, status (VALID / UNUSABLE), and index_type (NORMAL, BITMAP, FUNCTION-BASED).

SET VERIFY OFF LINESIZE 200
COLUMN table_owner     FORMAT a20
COLUMN index_owner     FORMAT a20
COLUMN index_type      FORMAT a12
COLUMN tablespace_name FORMAT a20

ACCEPT p_schema CHAR PROMPT 'Schema: '
ACCEPT p_table  CHAR PROMPT 'Table name (or ALL): '

SELECT table_owner,
       table_name,
       owner AS index_owner,
       index_name,
       tablespace_name,
       num_rows,
       status,
       index_type
FROM   dba_indexes
WHERE  table_owner = UPPER('&&p_schema')
AND    table_name  = DECODE(UPPER('&&p_table'), 'ALL', table_name, UPPER('&&p_table'))
ORDER BY table_owner, table_name, index_owner, index_name;

SET VERIFY ON