It’s likely that the slowest part could be Oracle exporting it’s data. Try to use sqlplus to export the data and see how long does it take, you won’t be able to make the process faster than Oracle can export it’s data.
If it’s fast enough, format the resulting file in a suitable format for Postgres ‘COPY FROM’ command.
Finally you can pipe the Oracle export command and the Postgres COPY FROM command, so the process can run twice as fast.
You can make it even faster if you divide the exported data by any criteria and run those export | import scripts in parallel.
Inserting directly into the partition didnt help, the performance are just the same. I tried to increase the prefetch value to 1000(alter foreign table hist_oracle options (add prefetch '1000') but still no change - 15 minutes for one partition(6GB).
On the oracle side the plan is full scan on the partition (I'm copying the entire partition into a postgresql partition..)