1

I have an Oracle 11g partitioned table with 10 partitions for ten years of data, each on its own tablespace partitioned by range. Each year-partition contains 12 monthly-partitions.

I would like to convert this table to a non-partitioned table, before migrating all the database to Postgresql 10.7 with ora2pg.

I've read that I could first backup this table by expdp and then import it using PARTITIONS_OPTIONS parameter option of impdp.

But is it also possible to use this following statement as a strict equivalent ?

CREATE TABLE IF NOT EXISTS non_partitioned_table AS SELECT * FROM partitioned_table

I would not lose any data, but what about the indexes ?

Is there other differences between these two procedures ?

LBS
  • 518
  • 8
  • 17

2 Answers2

1

Syntax you posted doesn't exist in Oracle (there's no if not exists clause there).

Therefore, you'd

create table non_partitioned_table as select * from partitioned_table;

If object whose name is non_partitioned_table already exists, that command would fail.

No indexes would be created automatically - you'd have to create them manually, but - you'd do that in PostgreSQL anyway, wouldn't you? Why bother in Oracle as you won't be using that table for anything (except migration purposes); right?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thank you for your helpful answer. Do you see any other difference ? I'm writing a script-shell to do the migration and the create statement seems simpler than the expdp/impdp method. – LBS Feb 14 '22 at 07:24
  • 1
    Data pump (expdp / impdp) work in Oracle only. I don't think you can import that .DMP file into PostgreSQL, so ... there's no point in considering it at all. At least, that's what I think. – Littlefoot Feb 14 '22 at 07:32
  • I would datapump in Oracle before migrating with ora2pg to Postgresql. – LBS Feb 14 '22 at 07:37
  • OK, but - why? You'd have the same data in two tables already: partitioned table, and non-partitioned table. There's nothing wrong in keeping it in a dump file, though, so - if you want, go for it - export it. – Littlefoot Feb 14 '22 at 07:44
  • The question is not really about the data, but about the data-partitioning. I take advantage of the migration to remove the data-partitioning of this table. Method 1: expdp table + impdb table with PARTITION_OPTIONS=merge + ora2pg. Method 2 : create table non_partitioned + drop table partitioned + ora2pg. I will try method 2. – LBS Feb 14 '22 at 10:22
1

You can use expdp to export the PARTITION table.

Then use impdp with the MERGE option to import it back into a non partition table. How you get the data into postgres is up to you.


expdp TABLES=scott.part_tab USERID="' / as sysdba'" DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=part_tab.log


impdp USERID="'/ as sysdba'" TABLES=scott.part_tab DIRECTORY=test_dir DUMPFILE=part_tab.dmp LOGFILE=imp_part_tab.log REMAP_SCHEMA=scott:testuser
Pugzly
  • 844
  • 3
  • 14