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