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.03k stars 341 forks source link

incorrect query generated #802

Closed oebeledrijfhout closed 5 years ago

oebeledrijfhout commented 5 years ago

I'm using the import_all.sh script to import a large (approximately 1.2TB) Oracle 12c database into PostgreSQL 11, using direct connection. Data copy goes well, but at some point during index/constraint creation, ora2pg appears to create an incorrect query and exits with an error:

ORA-08177: can't serialize access for this transaction (DBD ERROR: error possibly near <*> indicator at char 135 in 'SELECT DISTINCT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE, LAST_NUMBER,SEQUENCE_OWNER FROM <*>ALL_SEQUENCES WHERE SEQUENCE_OWNER='FRANCO_BACKUP' AND ( NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p1) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p2) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p3) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p4) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p5) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p6) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p7) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p8) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p9) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p10) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p11) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p12) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p13) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p14) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p15) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p16) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p17) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p18) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p19) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p20) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p21) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p22) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p23) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p24) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p25) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p26) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p27) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p28) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p29) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p30) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p31) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p32) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p33) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p34) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p35) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p36) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p37) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p38) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p39) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p40) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p41) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p42) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p43) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p44) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p45) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p46) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p47) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p48) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p49) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p50) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p51) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p52) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p53) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p54) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p55) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p56) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p57) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p58) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p59) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p60) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p61) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p62) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p63) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p64) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p65) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p66) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p67) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p68) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p69) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p70) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p71) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p72) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p73) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p74) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p75) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p76) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p77) AND NOT REGEXP_LIKE(UPp93) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p94) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p95) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p96) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p97) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p98) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p99) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p100) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p101) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p102) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p103) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p104) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p105) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p106) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p107) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p108) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p109) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), :p110))') [for Statement "SELECT DISTINCT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE, LAST_NUMBER,SEQUENCE_OWNER FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER='FRANCO_BACKUP' AND ( NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?) AND NOT REGEXP_LIKE(UPPER(SEQUENCE_NAME), ?))"] at /usr/share/perl5/vendor_perl/Ora2Pg.pm line 8762.

This query should work without the '<*>'. Is there a workaround for this bug? Is it a bug?

darold commented 5 years ago

Hi,

The '<*>' is inserted in the error message by Oracle to indicate where the problem is located. Here the problem is that data export takes to much time and your Oracle database is still in used. Oracle fail to serialize the transaction all the export time. You should use a read only transaction for your tests and stop any other activity on Oracle side when with the final migration. Set TRANSACTION in ora2pg.conf to readonly.

oebeledrijfhout commented 5 years ago

Thank you for the clarification. Probably the automatic backup interfered, other than that there were only some SELECT queries running on the Oracle DB.