RE: AW: Removing table partition

Smarter folks than I on here, but I’m sure there’s a way.

From: Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk> Sent: Friday, March 20, 2020 10:56 AM
To: Jeff Smith <jeff.d.smith_at_oracle.com> Cc: oracle-l-freelists <oracle-l_at_freelists.org> Subject: AW: Removing table partition

Thanks Jeff ... I would of course have to drop existing constraints and indexes, then recreate them all afterwards too.

Guess I have to fess up as well, it isn't one table, but around 400, albeit quite small (ranging from a few dozen to a few million rows). I will likely script it all as a last resort. I am just a little aghast that implementing partitions on existing tables appears to be natively supported whereas going the other way ... you cannot, it's a one way road.

Mike

HYPERLINK "https://urldefense.com/v3/__http:/www.strychnine.co.uk__;!!GqivPVa7Brio!JOA0jrhXb5Cug9t7yRwE7FLl42LDiDMxr8T2-A0bHoWCTDA9to8FL6r7OT-0AVcT4mI$"http://www.strychnine.co.uk

Am 20.03.2020 um 14:45 schrieb Jeff Smith <HYPERLINK "mailto:jeff.d.smith_at_oracle.com"jeff.d.smith_at_oracle.com>:

CTAS would nuke the partitions…since you’re say ‘XE’, I’m assuming you don’t have THAT much data.

From: Michael D O'Shea/Woodward Informatics Ltd <HYPERLINK "mailto:woodwardinformatics_at_strychnine.co.uk"woodwardinformatics_at_strychnine.co.uk>  Sent: Friday, March 20, 2020 10:27 AM
To: oracle-l-freelists <HYPERLINK "mailto:oracle-l_at_freelists.org"oracle-l_at_freelists.org> 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

HYPERLINK "https://urldefense.com/v3/__http:/www.strychnine.co.uk__;!!GqivPVa7Brio!MlyjbYtYDIDGlE2L2K2fddO8GAAogZYl2qJ86HmfK8_VALw3UKwyHsBEbd3E4ilqaFk$"http://www.strychnine.co.uk

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