search

darold / ora2pg

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.
http://www.ora2pg.com/
GNU General Public License v3.0
1.02k stars 343 forks source link

Some indexes are "missing" from the ora2pg export #178

Closed Ahuri3 closed 8 years ago

Ahuri3 commented 8 years ago

Hello,

I am migrating an Oracle database to PostgreSQL and ora2pg seems to ¨skip¨ some indexes (and I don´t know why).

Here is an example with one of my tables :

Oracle : 

select VISIBILITY, INDEX_NAME, STATUS from DBA_INDEXES where TABLE_NAME = 'EVE';

VISIBILIT INDEX_NAME                     STATUS
--------- ------------------------------ --------
VISIBLE   EVE_IDX1                       VALID
VISIBLE   EVE_IDX4                       VALID
VISIBLE   EVE_IDX5                       VALID
VISIBLE   EVE_IDX6                       VALID
VISIBLE   EVE_IDX10                      VALID
VISIBLE   EVE_IDX11                      VALID
VISIBLE   EVE_IDX12                      VALID
VISIBLE   EVE_IDX14                      VALID
VISIBLE   WI_EVE_IDX6                    VALID
VISIBLE   WI_EVE_IDX7                    VALID
VISIBLE   WI_EVE_IDX8                    VALID
VISIBLE   WI_EVE_IDX9                    VALID
VISIBLE   WI_EVE_IDX10                   VALID
VISIBLE   WI_EVE_IDX11                   VALID
VISIBLE   WI_EVE_IDX12                   VALID

PostgreSQL :

grep "ON eve" INDEXES_table.sql
CREATE INDEX eve_idx4 ON eve (codsoc,achvto,typevo,numevo);
CREATE INDEX eve_idx5 ON eve (codsoc,achvts,typevs,numevs,ordevs,achvte,typeve,numeve);
CREATE INDEX eve_idx6 ON eve (codsoc,typtie,sigtie);
CREATE INDEX eve_idx10 ON eve (codsoc,achvte,typeve,codeop);
CREATE INDEX eve_idx11 ON eve (codsoc,p_ecrnum);
CREATE INDEX eve_idx12 ON eve (codsoc,achvte,typeve,codeta);
CREATE INDEX wi_eve_idx6 ON eve (codsoc,achvte,typeve,batvol);
CREATE INDEX wi_eve_idx7 ON eve (codsoc,achvte,typeve,indint,codeta);
CREATE INDEX wi_eve_idx8 ON eve (codsoc,achvte,typeve,dateve,codeta);
CREATE INDEX wi_eve_idx9 ON eve (codsoc,batvol,achvte,typeve);
CREATE INDEX wi_eve_idx10 ON eve (codsoc,typtie,sigtie,typeve,refext);
CREATE INDEX wi_eve_idx11 ON eve (codsoc,typtie,sigtie,achvte,typeve,libcom);
CREATE INDEX wi_eve_idx12 ON eve (codsoc,achvte,typeve,sigdep,modliv,datexp);

The indexes EVE_IDX1 and EVE_IDX14 are missing.

I used the script : export_schema.sh to export the tables.

[...]
Running: ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf
[========================>] 16/16 tables (100.0%) end of scanning.
[========================>] 16/16 tables (100.0%) end of table export.
[...]

I investigated one of the index : EVE_IDX1 to see if there was something strange going on and it´s a classic B-tree index.

I looked at other tables and my EVE table is not an isolated case, some indexes just seem to be ¨missed¨ by ora2pg.

My version of ora2pg : 17.1 My version of Oracle :

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Here is a diff of my ora2pg.conf file and a "untouched" one : 

ora2pg --init_project migration_template --project_base .
diff migration_GNXDUP/config/ora2pg.conf  migration_template/config/ora2pg.conf
22c22,23
< ORACLE_DSN    dbi:Oracle:host=X.X.X.X;sid=CENSORED

---
> # Set Oracle database connection (datasource, user, password)
> ORACLE_DSN    dbi:Oracle:host=mydb.mydom.fr;sid=SIDNAME
44c45
< EXPORT_SCHEMA 1

---
> EXPORT_SCHEMA 0
47c48
< SCHEMA        MY_SCHEMA_NAME

---
> SCHEMA        CHANGE_THIS_SCHEMA_NAME
59c60
< COMPILE_SCHEMA        1

---
> COMPILE_SCHEMA        0
489c490
< DATA_LIMIT    100000

---
> DATA_LIMIT    10000
596c597
< ORACLE_COPIES 12

---
> ORACLE_COPIES 1

I tried doing the export in VERBOSE but I got nothing of value : 

ora2pg -d -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf
Ora2Pg version: 17.1
Trying to connect to database: dbi:Oracle:host=X.X.X.X;sid=CENSORED
Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Force Oracle to compile schema before code extraction
Retrieving table information...
[1] Scanning table TABLE1 (19180 rows)...
[2] Scanning table TABLE2 (321 rows)...
[3] Scanning table TABLE3 (227057 rows)...
[4] Scanning table TABLE4 (0 rows)...
[5] Scanning table TABLE5 (0 rows)...
[6] Scanning table TABLE6 (0 rows)...
[7] Scanning table EVE (11605651 rows)...
[8] Scanning table TABLE7 (143651525 rows)...
[9] Scanning table TABLE8 (142431694 rows)...
[10] Scanning table TABLE9 (158436586 rows)...
[11] Scanning table TABLE10 (8349401 rows)...
[12] Scanning table TABLE11 (1986826 rows)...
[13] Scanning table TABLE12 (485 rows)...
[14] Scanning table TABLE13 (15559 rows)...
[15] Scanning table TABLE14 (0 rows)...
[16] Scanning table TABLE15 (122849845 rows)...
Dumping table TABLE1...
Dumping table TABLE2...
Dumping table TABLE3...
Dumping table TABLE4...
Dumping table TABLE5...
Dumping table TABLE6...
Dumping table EVE...
Dumping table TABLE7...
Dumping table TABLE8...
Dumping table TABLE9...
Dumping table TABLE10...
Dumping table TABLE11...
Dumping table TABLE12...
Dumping table TABLE13...
Dumping table TABLE14...
Dumping table TABLE15...
Dumping indexes to one separate file : INDEXES_table.sql
Dumping constraints to one separate file : CONSTRAINTS_table.sql

Do you have any idea why some indexes are "ignored" by ora2pg ?

Ahuri3 commented 8 years ago

I just updated to Ora2pg 17.3 and my issue is still there.

darold commented 8 years ago

Hi,

I guess that indexes EVE_IDX1 and EVE_IDX14 are used by a primary key or other constraints, in this case they will be automatically created by PostgreSQL. Can you post here the definition of the table and iindexes under Oracle?

Ahuri3 commented 8 years ago

Thank you for you fast answer !

Here is the definition fo the INDEX EVE_IDX1: 

CREATE UNIQUE INDEX "GNXDUP"."EVE_IDX1" ON "GNXDUP"."EVE" ("CODSOC", "ACHVTE",
 "TYPEVE", "NUMEVE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 182452224 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CENSORED"

And the table : 

 select DBMS_METADATA.GET_DDL('TABLE','EVE','GNXDUP') from DUAL;

  CREATE TABLE "GNXDUP"."EVE"
   (    "ACHVTE" VARCHAR2(1),
        "TYPEVE" VARCHAR2(3),
        "NUMEVE" NUMBER(*,0),
        "SIGTIE" VARCHAR2(12),
        "TYPTIE" VARCHAR2(3),
        "NUMVER" NUMBER(*,0),
        "DATEVE" VARCHAR2(8),
        "DATVAL" VARCHAR2(8),
        "DATCRE" VARCHAR2(8),
        "DATLIV" VARCHAR2(8),
        "TYPEVO" VARCHAR2(3),
        "NUMEVO" NUMBER(*,0),
        "NBRFAC" NUMBER(*,0),
        "TYPFAC" NUMBER(*,0),
        "CODURG" NUMBER(*,0),
        "CODETA" VARCHAR2(1),
        "CODCTG" VARCHAR2(2),
        "REFEXT" VARCHAR2(16),
        "NUMCNT" VARCHAR2(9),
        "CODDEV" VARCHAR2(3),
        "PARDEV" NUMBER,
        "NUMADR" NUMBER(*,0),
        "MODLIV" VARCHAR2(3),
        "SIGTRA" VARCHAR2(12),
        "MODTRA" VARCHAR2(2),
        "NBRREL" NUMBER(*,0),
        "NUMREL" NUMBER(*,0),
        "DATACO" VARCHAR2(8),
        "MONACO" NUMBER,
        "TAUESC" NUMBER,
        "DATCLI" VARCHAR2(8),
        "CODBLO" VARCHAR2(1),
        "DATBLO" VARCHAR2(8),
        "EXICVC" VARCHAR2(1),
        "EXICVM" VARCHAR2(1),
        "EXITXT" VARCHAR2(1),
        "CUMHT" NUMBER,
        "CUMTTC" NUMBER,
        "SIGREP" VARCHAR2(12),
        "CODANA" VARCHAR2(6),
        "MODRGL" VARCHAR2(2),
        "DELRGL" NUMBER(*,0),
        "CODDPT" VARCHAR2(1),
        "CODQUA" NUMBER(*,0),
        "TOTCOL" NUMBER(*,0),
        "SIGDEP" VARCHAR2(12),
        "DATMOD" VARCHAR2(8),
        "UTIMOD" VARCHAR2(8),
        "POSFIS" VARCHAR2(1),
        "TOTHT" NUMBER,
        "CODTVA" VARCHAR2(1),
        "CUMPR" NUMBER,
        "LIBCOM" VARCHAR2(30),
        "TAUREM" NUMBER,
        "MONREM" NUMBER,
        "CODANA1" VARCHAR2(6),
        "CODANA2" VARCHAR2(6),
        "TAUCOM" NUMBER,
        "TYPREM" VARCHAR2(1),
        "ACHVTO" VARCHAR2(1),
        "PERFAC" VARCHAR2(1),
        "CAMION" VARCHAR2(10),
        "INDAPR" VARCHAR2(1),
        "TOTTTC" NUMBER,
        "DATEDI" VARCHAR2(8),
        "INDEDI" VARCHAR2(1),
        "INDSOL" VARCHAR2(1),
        "ACHVTS" VARCHAR2(1),
        "TYPEVS" VARCHAR2(3),
        "NUMEVS" NUMBER(*,0),
        "PEREVE" NUMBER(*,0),
        "PERLIV" NUMBER(*,0),
        "INDEPU" VARCHAR2(1),
        "CODBAR" VARCHAR2(3),
        "DATEXP" VARCHAR2(8),
        "PERVAL" NUMBER(*,0),
        "PEREXP" NUMBER(*,0),
        "ANNEVE" NUMBER(*,0),
        "ANNLIV" NUMBER(*,0),
        "ANNVAL" NUMBER(*,0),
        "ANNEXP" NUMBER(*,0),
        "ACHBCH" VARCHAR2(1),
        "TYPBCH" VARCHAR2(3),
        "NUMBCH" NUMBER(*,0),
        "INDINT" VARCHAR2(1),
        "DELLIV" NUMBER(*,0),
        "DELENG" NUMBER(*,0),
        "SIGMAG" VARCHAR2(12),
        "DATRGL" VARCHAR2(8),
        "CODTRN" VARCHAR2(6),
        "CODPOS" VARCHAR2(5),
        "MONREMFAC" NUMBER,
        "TAUREM2" NUMBER,
        "TAUREM3" NUMBER,
        "DATENG" VARCHAR2(8),
        "SIGACT" VARCHAR2(12),
        "GELEVE" VARCHAR2(1),
        "POSHIE" NUMBER(*,0),
        "EDITRT" VARCHAR2(1),
        "SIGVAL" VARCHAR2(12),
        "ORDEVS" NUMBER(*,0),
        "BATIMENT" VARCHAR2(2),
        "TYPLIV" VARCHAR2(3),
        "SIGLIV" VARCHAR2(12),
        "NUMATT" NUMBER(*,0),
        "CODSOC" NUMBER(*,0),
        "MODLOC" VARCHAR2(30),
        "FACREL" VARCHAR2(1),
        "CODASS" VARCHAR2(6),
        "EDICTS" VARCHAR2(1),
        "RELICA" VARCHAR2(1),
        "ETBCOD" VARCHAR2(3),
        "ETSSTATUT" VARCHAR2(1),
        "ACHVTT" VARCHAR2(1),
        "CODSIT1" VARCHAR2(3),
        "NUMVOY" NUMBER(*,0),
        "NUMFIL" NUMBER(*,0),
        "TAUAGIO" NUMBER,
        "MONESC" NUMBER,
        "MONAGIO" NUMBER,
        "TAUESCD" NUMBER,
        "NCOTAT" NUMBER(*,0),
        "TAUCA" NUMBER,
        "P_RIBCOD" VARCHAR2(3),
        "P_ECRNUM" NUMBER(*,0),
        "CODEOP" VARCHAR2(12),
        "NUMADR_I" NUMBER(*,0),
        "ACHVTEISE" VARCHAR2(1),
        "TYPISE" VARCHAR2(3),
        "NUMISE" NUMBER(*,0),
        "SIGTRS" VARCHAR2(12),
        "NUMLCR" NUMBER(*,0),
        "BATVOL" VARCHAR2(30),
        "CMPNIE" VARCHAR2(30),
        "NLTA" VARCHAR2(30),
        "DATDEP" VARCHAR2(8),
        "DATARR" VARCHAR2(8),
        "HEUARR" VARCHAR2(8),
        "HEUDEP" VARCHAR2(8),
        "GUIDRGL" VARCHAR2(3),
        "ACHVTE_CAT" VARCHAR2(1),
        "CODCAT" VARCHAR2(6),
        "HEUEXP1" NUMBER,
        "HEUEXP2" NUMBER,
        "HEUEVE" NUMBER,
        "HEUVALI" NUMBER,
        "HEULIV1" NUMBER,
        "HEULIV2" NUMBER,
        "TYPGEL" VARCHAR2(1),
        "NUMGEL" NUMBER(*,0),
        "CODRGM" VARCHAR2(1 CHAR),
        "CODVHC" VARCHAR2(1 CHAR),
        "CHRONO" NUMBER(*,0),
        "NUMCTC" NUMBER(*,0),
        "TYPCPR" VARCHAR2(3 CHAR),
        "CODCB" VARCHAR2(25 CHAR),
        "STATUT6" VARCHAR2(6 CHAR),
        "MODAPPRO" VARCHAR2(1 CHAR),
        "SIGLIE_EMB" VARCHAR2(12 CHAR),
        "SIGLIE_DEB" VARCHAR2(12 CHAR),
        "CODPAY_DEP" VARCHAR2(3 CHAR),
        "CODPAY_EMB" VARCHAR2(3 CHAR),
        "CODPAY_DEB" VARCHAR2(3 CHAR),
        "CODJAL" VARCHAR2(6 CHAR),
        "INDFAC" VARCHAR2(1 CHAR),
        "ACHVTA" VARCHAR2(1 CHAR),
        "TYPEVA" VARCHAR2(3 CHAR),
        "NUMEVA" NUMBER(*,0),
         CONSTRAINT "EVE_IDX1" PRIMARY KEY ("CODSOC", "ACHVTE", "TYPEVE", "NUMEVE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 182452224 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "POCORA"  ENABLE,
         CONSTRAINT "EVE_IDX14" UNIQUE ("CODSOC", "NUMLCR", "ACHVTE", "TYPEVE", "NUMEVE
")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 240123904 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "POCORA"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 783728640 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CENSORED"

So it looks like there are indeed CONSTRAINTs. So the indexes will automatically created by PostgreSQL ?

darold commented 8 years ago

Yes, PostgreSQL create an implicite index when a primary key is defined, this is why the index is not exported. In the CONSTRAINTS_table.sql file you will see the PRIMARY KEY definition.

To check that every thing is the same you can use the TEST export type, Ora2Pg will compare the number of indexes and constraints between source and destination database. See documentation for more explanation.

Ahuri3 commented 8 years ago

Thank you very much !

MINSIKKANG commented 2 years ago

Hi! Master, I have the same problem, what document should I see?