How to find index with same columns but different name between two oracle databases.

How to find index with same columns but different name between two databases.

select
targetDB.idx targetDB_index, targetDB.tbl targetDB_table, targetDB.cols targetDB_cols, sourceDB.idx sourceDB_index, sourceDB.tbl sourceDB_table, sourceDB.cols sourceDB_cols,
'ALTER INDEX ' || targetDB.idx || ' RENAME TO ' || sourceDB.idx || ';' sqlchg
from
(select
index_name idx,
table_name tbl,
ltrim(max(sys_connect_by_path(column_name, ','))
keep (dense_rank last order by curr), ',') as cols
from
(select
index_name,
table_name,
column_name,
row_number() over (partition by index_name, table_name order by column_position) as curr,
row_number() over (partition by index_name, table_name order by column_position) -1 as prev
from
user_ind_columns@sourceDB
where index_name not like 'SYS_%')
group by index_name, table_name
connect by prev = PRIOR curr and index_name = PRIOR index_name and table_name = PRIOR table_name
start with curr = 1
order by idx, tbl) sourceDB,
(select
index_name idx,
table_name tbl,
ltrim(max(sys_connect_by_path(column_name, ','))
keep (dense_rank last order by curr), ',') as cols
from
(select
index_name,
table_name,
column_name,
row_number() over (partition by index_name, table_name order by column_position) as curr,
row_number() over (partition by index_name, table_name order by column_position) -1 as prev
from
user_ind_columns
where index_name not like 'SYS_%')
group by index_name, table_name
connect by prev = PRIOR curr and index_name = PRIOR index_name and table_name = PRIOR table_name
start with curr = 1
order by idx, tbl) targetDB
where
targetDB.tbl = sourceDB.tbl
and targetDB.cols = sourceDB.cols
and targetDB.idx != sourceDB.idx
order by targetDB.idx, targetDB.tbl;