how would i compare if two indexes are identical?

What is this?

Hi manik,

Can you please check this:

I am using below query to compare two indexes from 2 different users but even though index name and columns are same... result shows me they are different.. what I am doing wrong? Thanks

WITH t AS

        (SELECT COUNT (DISTINCT index_owner || index_name || indexed_cols)

                   cnt

           FROM (  SELECT index_owner,

                          index_name,

                          listagg (column_name, ',')

                             WITHIN GROUP (ORDER BY column_position)

                             indexed_cols

                     FROM dba_ind_columns

                    WHERE index_name='XPKTBL_A'

                 GROUP BY index_owner, index_name))

SELECT CASE

          WHEN cnt > 1 THEN 'Indexes are different'

          WHEN cnt = 0 THEN 'Indexes dont exist'

          WHEN cnt = 1 THEN 'Indexes are identical'

       END

          commnt

  FROM t

Result:

Indexes are different

but Actually if you check below they are same After when I run this query:

SELECT index_owner,

         index_name,

         listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position)

            indexed_cols

    FROM dba_ind_columns

   WHERE index_name='XPKTBL_A'

GROUP BY index_owner, index_name;

Result: