Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Erhan_torontoJul 25 2013 — edited Jul 30 2013

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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 27 2013
Added on Jul 25 2013
2 comments
441 views