rem -----------------------------------------------------------------------
rem Filename:   cmpindx.sql
rem Purpose:    Compare indexes on two databases and list the differences
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pagesize 50000 veri off echo off

col table_name      format a30
col column_name     format a30
col column_position format 990

accept DB1 prompt "Enter database name to compare from: "
accept DB2 prompt "Enter database name to compare to: "

spool cmpindx.lst

prompt ======================================================================
prompt == Tables/ columns with indexes on &DB1 that's not on &DB2
prompt ======================================================================

select table_owner||'.'||table_name table_name, column_position, column_name
  from dba_ind_columns@&DB1.
 where table_owner not in ('SYS', 'SYSTEM')
minus
select table_owner||'.'||table_name table_name, column_position, column_name
  from dba_ind_columns@&DB2.
 where table_owner not in ('SYS', 'SYSTEM')
/

prompt ======================================================================
prompt == Tables/ columns with indexes on &DB2 that's not on &DB1
prompt ======================================================================

select table_owner||'.'||table_name table_name, column_position, column_name
  from dba_ind_columns@&DB2.
 where table_owner not in ('SYS', 'SYSTEM')
minus
select table_owner||'.'||table_name table_name, column_position, column_name
  from dba_ind_columns@&DB1.
 where table_owner not in ('SYS', 'SYSTEM')
/

spool off