How to use Query Type?

[dschaell]

Hey, i got the following problem: I want to Use the query command in my ora2pg.conf File:

ora2Pg.conf File:

---

ORACLE_DSN dbi:Oracle:host=host;service_name=servicename;port=port ORACLE_USER user ORACLE_PWD password

PG_DSN dbi:Pg:dbname=PostgresDB;host=localhost;port=5432 PG_USER postgres PG_VERSION 15

SCHEMA Schema

DATA_ONLY 1 REPLACE_QUERY Table1[SELECT Col1,Col2,Col3,Col5 FROM Table1 WHERE Time_Created >= ADD_MONTHS(SYSDATE, -8)] DEBUG 1 LOG_ON_ERROR 1 TABLES Table1

---

I want to migrate only data which is not older than 8 months, because the migration would take way too long if i want to migrate the last 10 years. Are there ways to import fata much faster? I have a table with more than 1.400.000.000 rows and nearly 40GB of Diskspace. I get with COPY command 15k recs/sec which means i will need round about 26h to migrate only 1 table of the entire DB.

I run ora2pg with the following command:

ora2pg -c .\ora2pg.conf -l .\Logfile -t QUERY

in the Logfile there is only one line:

-- Nothing found of type QUERY

[darold]

Hi @dschaell you should use the following for this big table:

WHERE        TABLE1[Time_Created >= ADD_MONTHS(SYSDATE, -8)]

then you can use -P to parallelize all table export.

If only this table export take most of the time to export data then you must export it separately from the other tables and use -J to parallelize the Oracle data export from this t. Use -e table1 to exclude this table from the whole tables export and -a table1 to allow data export from this table only.

The more CPU you have to parallelize the faster data migration will be done. For example -P 8 will process 8 tables in parallel at a time and -J 8 will split and parallelize data export from your table on 8 processes.

[dschaell]

Hi @darold thanks for your fast response, i will try this out. I have another thing. I want to migrate only specific columns, not all. Thats the reason why i used the SELECT statement and the REPLACE QUERY and not the WHERE statement.

In my Oracle DB there are for example 10 columns but in my PG DB there are only like 6 columns. (I dont need that data which is in the other 4 cols which are not in the PG DB)