Smarter folks than I on here, but I’m sure there’s a way.
From: Michael D O'Shea/Woodward Informatics Ltd
<woodwardinformatics@xxxxxxxxxxxxxxxx>
Sent: Friday, March 20, 2020 10:56 AM
To: Jeff Smith <jeff.d.smith@xxxxxxxxxx>
Cc: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxx"jeff.d.smith@xxxxxxxxxx>:
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@xxxxxxxxxxxxxxxx"woodwardinformatics@xxxxxxxxxxxxxxxx>
Sent: Friday, March 20, 2020 10:27 AM
To: oracle-l-freelists <HYPERLINK
"mailto:oracle-l@xxxxxxxxxxxxx"oracle-l@xxxxxxxxxxxxx>
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