I have 2 schemas Test1 and Test2.
In both schemas i have the same 3 tables Author, Book, AuthorBookLink.
Author table has (ID varchar2, Name, varchar2) - ID primary key
Book table has (ID varchar2, Name, varchar2)- ID primary key
AuthorBookLink table has (AUTH_ID varchar2, BOOK_ID varchar2)- AUTH_ID,BOOK_ID composite primary key
dbms_comparison.create_comparison(
comparison_name=>'TESTCOMPARE',
schema_name=>'Test1',
object_name=>'AuthorBookLink',
dblink_name=>null,
remote_schema_name=>'Test2',
remote_object_name=>'AuthorBookLink');
now when i try to compare AuthorBookLink table using DBMS_COMPARISON.CREATE_COMPARISON, it gives me error
ORA-23629: Test2.AuthorBookLink_P is not an eligible index on table Test2.AuthorBookLink for comparison
As per https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_comparison.htm#CJHFEEGA
For the scan modes CMP_SCAN_MODE_FULL and CMP_SCAN_MODE_CUSTOM to be supported, the database objects must have one of the following types of indexes:
A single-column index on a number, timestamp, interval, DATE, VARCHAR2, or CHAR datatype column
A composite index that only includes number, timestamp, interval, DATE, VARCHAR2, or CHAR columns. Each column in the composite index must either have a NOT NULL constraint or be part of the primary key.
How can i solve this issue?