Hi,
I am trying to a comparison between two tables which are present on same DB using below create_comparison procedure
begin
dbms_comparison.create_comparison(
comparison_name => 'comp_dbms_test',
SCHEMA_NAME => 'DEV06_OWNER',
OBJECT_NAME => 'facility_prod_temp',
INDEX_SCHEMA_NAME => 'DEV06_OWNER',
INDEX_NAME => 'idx_test',
dblink_name => null,
REMOTE_SCHEMA_NAME => 'DEV06_OWNER',
remote_object_name => 'facility_test_temp',
column_list => '*',
SCAN_MODE => DBMS_COMPARISON.CMP_SCAN_MODE_FULL);
end;
and getting below error --
Error report:
ORA-23629: DEV06_OWNER.IDX_TEST is not an eligible index on table DEV06_OWNER.FACILITY_PROD_TEMP for comparison
ORA-06512: at "SYS.DBMS_COMPARISON", line 5008
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 2
Here is the structure of the table :
desc FACILITY_PROD_TEMP;
Name Null Type
------------------- ---- ------------------
PARTITION_KEY VARCHAR2(12)
CONTRACT_REFERENCE VARCHAR2(101 CHAR)
CONTRACT_DESC VARCHAR2(100 CHAR)
ATTRIBUTE_3 VARCHAR2(100 CHAR)
ATTRIBUTE_1 VARCHAR2(100 CHAR)
COMMITED VARCHAR2(10 CHAR)
ATTRIBUTE_6 VARCHAR2(100 CHAR)
ATTRIBUTE_7 VARCHAR2(100 CHAR)
ATTRIBUTE_8 VARCHAR2(100 CHAR)
VALUE_DATE VARCHAR2(30)
ATTRIBUTE_5 VARCHAR2(100 CHAR)
LGD VARCHAR2(30)
DIM16 VARCHAR2(100 CHAR)
MATURITY_DATE VARCHAR2(50)
DIM9 VARCHAR2(100 CHAR)
IMPORT_SOURCE VARCHAR2(100 CHAR)
I have created a index on contract_reference column using below syntax -
create index idx_test on facility_prod_temp (contract_reference);
Oracle Doc says :
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 data type column
According to which I have created a index on CONTRACT_REFERENCE column which is of varchar2 type.
I am using -
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production