It depends what you're trying to achieve.
If you're trying to eliminate partitioning to remove the need for a licence then you're going to have to do something a bit messy.
If you just want to reduce each table to a single segment you could run a statement like the following for each table:
alter table t1 modify partition by hash ({primary key column}) partitions 1 update indexes online;
This gives you a single data segment for each table and index, even though the table is technically still partitioned.
If you want to reduce to a simple table then you'll need to create a table "for exchange", and exchange - but that would need you to worry about cleaning up all the details of foreign keys.
Note: 18c XE may not allow ONLINE.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk>
Sent: 20 March 2020 14:26
To: oracle-l-freelists
Subject: Removing table partition
Chaps, this is an RTFM question I know, but I am also having no luck.
Scenario: Oracle 18c XE, Win x64, dev. environment, existing list partitioned table with many FK's to and from other tables.
There seems to be no obvious functionality such as, in pseudo code, ALTER TABLE COALESCE PARTITION FLATTEN or other such DDL to remove existing partitions and return to an unpartitioned table. DBMS_REDEFINITION seemed like an obvious port of call too, but the functionality I seek doesn't seem to be on offer. Am I missing something?
Mike
http://www.strychnine.co.uk
