Re: How to compare index columns from different users with same table name.... if the index columns

Erhan_toronto

I am using 11gR2...

UserA:

create table TBL_A (       FIELD_A1 number not null,

FIELD_A2 varchar2(50),

FIELD_A3 date,

FIELD_A4 number(5,2) default 0,

FIELD_A5 varchar2(10) not null

                                );

                                create unique index XPKTBL_A on TBL_A (FIELD_A1);

UserB:

create table TBL_A (       FIELD_A1 number not null,

FIELD_A2 varchar2(20),

FIELD_A4 number(5,2) not null,

FIELD_A5 varchar2(10),

FIELD_A6 number(2) not null

                                );

                                create unique index XPKTBL_A on TBL_A (FIELD_A1, FIELD_A6);

I change index_owner to table_name still doesnt show the differences:

WITH t AS

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

                   cnt

           FROM (  SELECT table_name,

                          index_name,

                          listagg (column_name, ',')

                             WITHIN GROUP (ORDER BY column_position)

                             indexed_cols

                     FROM dba_ind_columns

                    WHERE index_name='XPKTBL_A' and table_name= 'TBL_A'

                 GROUP BY table_name, 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 identical

Karthick2003

Try this

select decode

       (

            sum(decode(column_name_1, column_name_2, 0, 1))

          , 0

          , 'Identical'

          , null

          , 'Not exist'

          , 'Different'

       ) index_status

  from (

          select column_position

               , max(decode(index_owner, 'USERA' , column_name)) column_name_1

               , max(decode(index_owner, 'USERB', column_name)) column_name_2

            from all_ind_columns

           where table_name = 'TBL_A'

           group

              by column_position

       )

Erhan_toronto

Thanks Karthick for your reply and sorry for delayed response...