Hello,
I have Oracle 11.2 EE on Redhat 5.9 and I need to solve problem with partitioning.
Few tables of some system was prepared for partitioning few years ago. But no partitioning was made and all of these tables/global indexes have only one partition for all data.
Now we have many tables and indexes with only one partition (with MAXVALUE limit) for all data. I would like to split this big partition to more smaller partitions by quarter of year.
Example:
Existing partition D0201_2008_1Q should be split to D0201_2008_2Q, D0201_2008_3Q ... MYTABLE_2014_4Q ... by DATE/NUMBER column
I tried to generate script for splitting partitions
ALTER INDEX I_D0201 MODIFY DEFAULT ATTRIBUTES TABLESPACE INDX_2008_1Q;
ALTER TABLE D0201 SPLIT PARTITION D0201_2008_1Q AT (1000456)
INTO (PARTITION D0201_2008_XX TABLESPACE DATA_2008_1Q , PARTITION D0201_MAX1) PARALLEL 16;
ALTER TABLE D0201 MODIFY PARTITION D0201_2008_XX REBUILD UNUSABLE LOCAL INDEXES;
ALTER INDEX I_D0201 MODIFY DEFAULT ATTRIBUTES TABLESPACE INDX_2008_2Q;
ALTER TABLE D0201 SPLIT PARTITION D0201_MAX1 AT (1000547)
INTO (PARTITION D0201_2008_2Q TABLESPACE DATA_2008_2Q , PARTITION D0201_MAX2) PARALLEL 16;
ALTER TABLE D0201 MODIFY PARTITION D0201_2008_2Q REBUILD UNUSABLE LOCAL INDEXES;
ALTER INDEX I_D0201 MODIFY DEFAULT ATTRIBUTES TABLESPACE INDX_2008_3Q;
ALTER TABLE D0201 SPLIT PARTITION D0201_MAX2 AT (1000639)
INTO (PARTITION D0201_2008_3Q TABLESPACE DATA_2008_3Q , PARTITION D0201_MAX3) PARALLEL 16;
ALTER TABLE D0201 MODIFY PARTITION D0201_2008_3Q REBUILD UNUSABLE LOCAL INDEXES;
...
It split big partition to two new partitions. One of these is next quarter and second will be split again.
Some partitions have few GB and splitting takes a very long time (hours for one partition split) and big free disc space is also required.
New partitions will be smaller, but size of first partition 2008_1Q will be unchanged and I will need to reclaim unused space somehow.
Do you have some ideas better/faster solution?