set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 132
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT #################################################################
PROMPT --  Name          :  listSuperfluousIndexesInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   : This script lists all indexes that have the same leading
PROMPT                     column on a table and may be superfluous.
PROMPT
PROMPT --  Inputs(s)     :  None
PROMPT 
PROMPT --  Output       : The output is in a file named superfluousIndexesInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT #################################################################
PROMPT 

column table_owner   format a20  heading "Table Owner" wrap
column table_name    format a32  heading "Table Name"  wrap
column column_name   format a32  heading "Column Name" wrap

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on
set feedback off

set head on

spool superfluousIndexesInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "Indexes which may be Superfluous for "  &s_dbname  " on " &s_machine skip 2

break on table_owner skip 1

select table_owner,
       table_name,
       column_name
from  all_ind_columns
where column_position = 1
  and table_owner NOT IN ('SYS', 'SYSTEM')
group by table_owner, table_name , column_name
having count(*) > 1;

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty, no superfluous index information was found for the database &s_dbname
PROMPT
PROMPT
PROMPT Output file superfluousIndexesInfo_&s_dbname._&s_machine..LST produced
PROMPT

@sqlplus_settings