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 have 5 problems with TABLESPACE export type.
All errors are related to PARTITIONED (and SUBPARTITIONED) tables.
Ora2Pg skiped SUBPARTITIONED table. It just lost such type of tables in scripts of tablespace export.
Syntax
Ora2Pg generated script with syntax error. Word "PARTITION" shouldn't be in query:
Syntax
As I know, if I want to select tablespace for PARTITIONED table, in PostgreSQL I shoud indicate tablespace for each partition of this table. Ora2Pg generates queries WITHOUT NAMES OF PARTITIONS, it puts name of table instead:
Quantity of queries for partitions
Ora2Pg generates queries not only for each partition, but another quantity.
For example I have table in Oracle with 6 partitions, but Ora2Pg generated 49 equal lines of queries.
So even if they had the correct syntax, it's too much for 6 partitions.
Also I noticed an interesting fact: number of lines generated by Ora2Pg for partitioned tables always is a square number (4 partitions - 16 lines, 6 partiitons - 49 lines).
Oracle:
Ora2Pg generated code (not all lines):
Partitioned index
Listed above problems cover this situation, but I want to highlight it too.
I have partitioned index on partitioned table and I specified different tablespaces for different partitions of index.
Ora2Pg generated wrong queries (wrong syntax and wrong number of lines) for index tablespaces too.
darold
commented
1 year ago
I have 5 problems with TABLESPACE export type. All errors are related to PARTITIONED (and SUBPARTITIONED) tables.
Ora2Pg skiped SUBPARTITIONED table. It just lost such type of tables in scripts of tablespace export.
Syntax Ora2Pg generated script with syntax error. Word "PARTITION" shouldn't be in query:
Syntax As I know, if I want to select tablespace for PARTITIONED table, in PostgreSQL I shoud indicate tablespace for each partition of this table. Ora2Pg generates queries WITHOUT NAMES OF PARTITIONS, it puts name of table instead:
Quantity of queries for partitions Ora2Pg generates queries not only for each partition, but another quantity. For example I have table in Oracle with 6 partitions, but Ora2Pg generated 49 equal lines of queries. So even if they had the correct syntax, it's too much for 6 partitions. Also I noticed an interesting fact: number of lines generated by Ora2Pg for partitioned tables always is a square number (4 partitions - 16 lines, 6 partiitons - 49 lines).
Oracle:
Ora2Pg generated code (not all lines):
Oracle:
Ora2Pg generated code: