Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Best way to split big partition to few smaller

cardelJun 13 2014 — edited Jun 16 2014

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?

This post has been answered by Ramin Hashimzadeh on Jun 13 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 14 2014
Added on Jun 13 2014
8 comments
3,928 views