Oracle Database

--
-- Show info for a given index. (All indexes if 'ALL' is specified)
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN table_owner FORMAT A20
COLUMN index_owner FORMAT A20
COLUMN index_type FORMAT A12
COLUMN tablespace_name FORMAT A20
 
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('&Owner')
AND    table_name = DECODE(UPPER('&&Table_Name'), 'ALL', table_name, UPPER('&&Table_Name'))
ORDER BY table_owner, table_name, index_owner, index_name
/