I have two databases with indexes for same column different. I want a script that compares these two databases and generates the rename index script for the target database. Can you provide me a sample? Need to join dba_indexes and dba_ind_columns from source and target and compare and give a rename script from soruce to target
You can collapse the columns into a single string using LISTAGG
SQL> select index_name, table_name, listagg(column_name,':') within group ( order by column_position ) cols
2 from dba_ind_columns
3 where index_owner = 'MCDONAC'
4 group by index_name, table_name;
INDEX_NAME TABLE_NAME COLS
------------------------------ ------------------------------ -------------------------------------------------------
MY_ID MY_TABLE MY_ID
DIM_IX DIM_ACCT CUSTOMER_ID
FCT_IX FCT_TRANS CUSTOMER_ID:TRANS_DATE
PK_POLYGON POLYGON OWNER:POLYGON_ID
IE3_POLYGON POLYGON SYS_NC00033$
MY_BLOB_IDX MY_TABLE MY_BLOB
PK_ELEMENTS ELEMENTS ID
...
...
So once you have that, you can do the same on each database, and then look for differences
with
db1 as
(
select index_name, table_name, listagg(column_name,':') within group ( order by column_position ) cols
from dba_ind_columns
where index_owner = 'MCDONAC'
group by index_name, table_name
),
db2 as
(
select index_name, table_name, listagg(column_name,':') within group ( order by column_position ) cols
from dba_ind_columns@remote_database
where index_owner = 'MCDONAC'
group by index_name, table_name
)
select db1.index_name, db2.index_name
from db1, db2
where db1.cols = db2.cols
and db1.table_name = db2.table_name
and db1.index_name != db2.index_name;