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