Can I migrate a partitioned table to a non-partitioned table in Oracle with the CREATE TABLE statement?

Question

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 ?

Answer 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?

Answer 2

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