List Index

REM      script LI.SQL (list indexes)
REM      wildcards in table_name allowed,
REM      and a '%' is appended by default
REM      ======================================

set      termout  off
store    set sqlplus_settings replace
save     buffer.sql replace
set      verify off autotrace off
set      feedback off termout on
break    on table_name skip 1 on index_type

col table_name format a25
col index_name format a30
col index_type format a20

accept   table_name -
         prompt 'List indexes on table : '

SELECT   ui.table_name
,        decode(ui.index_type
               ,'NORMAL', ui.uniqueness
               ,ui.index_type) AS index_type
,        ui.index_name
FROM     user_indexes  ui
WHERE    ui.table_name LIKE upper('&table_name.%')
ORDER BY ui.table_name
,        ui.uniqueness desc;

get buffer.sql nolist
@sqlplus_settings
set termout on