AW: Removing table partition

Thanks for all the suggestions chaps.

The full story is that as part of an upgrade from 11g for a banking & finance organisation, I am also taking the opportunity to explore some obvious performance improvements. This includes the partitioning of a few large tables based on trade type, region, issuance programme, and so on. I was and remain surprised to find table partitioning is effectively a one way road and that a rollback script of sorts cannot not easily be implemented.

In the end I opted to get a fresh PROD clone, trim it down, import into XE, and again commence work afresh on my local dev. instance.

Regards

Mike

http://www.strychnine.co.uk <http://www.strychnine.co.uk/>

> Am 20.03.2020 um 18:32 schrieb Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>:
>
>
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l