Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
I made a synthetic example for test purpose TEST option. It is important that the tables differ in name based on the destination and source, but the REPLACE_TABLES instruction is used. Below is the sequence of steps to reproduce the issue:
In the UFS_PARAMS_CONFIG_SB schema, a table is created on the Oracle database:
create table scope_test1
(col1 NUMBER(19) not null,
col2 VARCHAR2(128 CHAR) not null
);
alter table scope_test1 add constraint scope_test1_col1_pk primary key (col1);
create index scope_test1_col1_col2_idx on scope_test1 (col1, col2);
A table is created in the cfge_sb schema on the PG:
create table cfge_test1
(col1 integer not null,
col2 varchar(128) not null
);
alter table cfge_test1 add constraint cfge_test1_col1_pk primary key (col1);
create index cfge_test1_col1_col2_idx on cfge_test1 (col1, col2);
Setup the configuration file ora2pg.conf with REPLACE_TABLES for mapping:
# You may wish to change table names during data extraction, especally for
# replication use. Give a list of tables separate by space as follow.
#REPLACE_TABLES ORIG_TB_NAME1:NEW_TB_NAME1 ORIG_TB_NAME2:NEW_TB_NAME2
REPLACE_TABLES scope_test1:cfge_test1
The ora2pg utility is launched in data structure comparison mode:
ora2pg -d -c /etc/ora2pg/ora2pg.conf -t TEST
The result of the comparison, for example, in indexes is incorrect:
[2022-12-08 16:53:30] Looking for objects count related to source database and PostgreSQL tables...
[2022-12-08 16:53:30] Collecting 1 tables in ALL_OBJECTS took: 0 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU)
[2022-12-08 16:53:30] Collecting 1 tables information in ALL_TABLES took: 0 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU)
[TEST COLUMNS COUNT] ORACLEDB:SCOPE_TEST1:2
[2022-12-08 16:53:30] Replacing table SCOPE_TEST1 as cfge_test1... POSTGRES:cfge_test1 (origin: SCOPE_TEST1):0
[ERRORS COLUMNS COUNT] Table cfge_test1 (origin: SCOPE_TEST1) doesn't have the same number of columns in source database (2) and in PostgreSQL (0). PostgreSQL modified struct: CFGE_TEST1 (ORIGIN: SCOPE_TEST1)() [TEST INDEXES COUNT]
[2022-12-08 16:53:50] Collecting 3 indexes in ALL_INDEXES took: 20 wallclock secs ( 0.01 usr + 0.00 sys = 0.01 CPU) ORACLEDB:SCOPE_TEST1:2
[2022-12-08 16:53:50] Replacing table SCOPE_TEST1 as cfge_test1... POSTGRES:cfge_test1 (origin: SCOPE_TEST1):0 [ERRORS INDEXES COUNT] Table cfge_test1 (origin: SCOPE_TEST1) doesn't have the same number of indexes in source database (2) and in PostgreSQL (0).
Hi
I made a synthetic example for test purpose TEST option. It is important that the tables differ in name based on the destination and source, but the REPLACE_TABLES instruction is used. Below is the sequence of steps to reproduce the issue: