Hi,
In End of June, 2022 - On deciding to split multiple tables of an Oracle user in 11gR2 database into Monthly partitions, I created 12 monthly partition tablespaces as follows:
GBD_PARTITIONED_2022
GBD_PARTITIONED_202201 GBD_PARTITIONED_202202 GBD_PARTITIONED_202203
GBD_PARTITIONED_202204 GBD_PARTITIONED_202205 GBD_PARTITIONED_202206
GBD_PARTITIONED_202207 GBD_PARTITIONED_202208 GBD_PARTITIONED_202209
GBD_PARTITIONED_202210 GBD_PARTITIONED_202211 GBD_PARTITIONED_202212
GBD_PARTITIONED_UNLIMITED
and issued following statement to split each tables of this user for Jan 2022
ALTER TABLE GBD.SALES SPLIT PARTITION GBDPART_UNLIMITED
AT (TO_DATE('2022-02-01', 'YYYY-MM-DD'))
INTO (partition GBDPART_202201 tablespace GBD_PARTITIONED_202201, partition GBDPART_UNLIMITED)
UPDATE GLOBAL INDEXES;
The tables got partitioned and data for Jan 2022 went to Jan partition GBDPART_202201 correctly.
Thereafter, on issuing statement for Jul 2022 (bypassing Feb, Mar, Apr, May, Jun months - thinking will work on these months later):
ALTER TABLE GBD.SALES SPLIT PARTITION GBDPART_UNLIMITED
AT (TO_DATE('2022-08-01', 'YYYY-MM-DD'))
INTO (partition GBDPART_202207 tablespace GBD_PARTITIONED_202207, partition GBDPART_UNLIMITED)
UPDATE GLOBAL INDEXES;
I see oracle has put the data of Feb, Mar, Apr, May, Jun also in July partition (in addition to July data).
Now it appears, I need to sub-partition July 2022 partition data into Feb, Mar, Apr, May, Jun respective sub-partitions
Will the following statement split July partition into Feb 2022 sub-partition OR need any modification? Please advise.
ALTER TABLE GBDREPO_RCO.SALES SPLIT PARTITION GBDPART_UNLIMITED
AT (TO_DATE('2022-08-01', 'YYYY-MM-DD'))
INTO (partition GBDPART_202207 tablespace GBD_PARTITIONED_202207, partition GBDPART_202202 tablespace GBD_PARTITIONED_202202)
UPDATE GLOBAL INDEXES;