Skip to Main Content


Question and Answer

Chris Saxon

Thanks for the question, Kashif.

Asked: January 20, 2021 - 5:06 pm UTC

Last updated: January 27, 2021 - 11:13 am UTC


Viewed 100+ times

You Asked


I have a production problem for which I need your inputs.
Version: Oracle Database 11g Enterprise Edition Release - 64bit Production

1) There is a monthly Partitioned Oracle table with monthly Partitions created at during table creation.
2) The application ran fine for many years but admin forgot the create new Partitions in time and now we have a very heavy MAX partition having 200 million rows.
There are a number of tables affected with this problem.
3) The application software provider gave us the standard PARTITION SPLIT command based scripts to split MAX partition into new monthly partition which can run recursively to run until the whole MAX partition is empty and future partitions are created.

ISSUE: For some heavy tables, SPLITTING even a single month with DOP 16 is taking almost 20 hours on Prod like Copy.
Such down time is not possible for this application and I would like to avoid that.

Is there any alternate approach possible which can help in faster Partition split.
--> we want minimum downtime.
--> The activity can be performed in a longer duration(like a month) as there is no application performance issue at the moment but no downtime is critical for the application. Maybe just 1 downtime for 4-5 hours.

Any input will be highly appreciated.

One approach I can think of is:-
1) Detach this MAX Partition (I tried using EXCHAHE Partition in my POC but This requires an un-partitioned shadow/target table)
2) Add New Partitions in orig tables.
3) Re-attach MAX Partition (how to re-attach?)
4) Later enable ROW MOVEMENT and UPDATE PARTITIOING key to make rows move into correct Partitions.-- this can be done slowly over few weeks

Will this approach work?

and we said...

I'm unsure what you mean by "detach" the max partition. Some form of partition exchange process where you gradually split the partition into smaller chunks is possible, but this will be fiddly.

Here's a couple of other options I'd choose over this:

Upgrade to 12.2 (or higher) where you can do partition splitting online :)

You've said there's no application performance issue at the moment. So if you prioritise upgrading (to be honest as you're on 11.2 this should be a high priority task anyway) you may be able to live with the situation as-is until then.

If you're not able to wait until after an upgrade, you could use DBMS_redefinition to change the partitioning method online.


  (1 rating)


Thanks for your input, FAST SPLIT

Kashif Akhtar, January 25, 2021 - 9:19 pm UTC

Thanks for your inputs.

We are now actually considering the speedup of DB upgrade for our project.
The IT has EXAData in pipeline which should be available in prod in 6 months.

1) Do you think having EXAData will help us further in Online Partition SPLIT or 12C VS 12C + ExaData will have same performance. AT the moment I am not sure whic oracle version will come with EXAData, but assuming at least it will be 12c.

2) In order to Stop further growth of MAX partition in next 6 months I am evaluating below option. Any feedback on this will be great.

a) Some rows in MAX Part has PARTITION KEY , way too far in future (due to bad functional input), but volume is very less, less like 5K rows in whole table.

UPDATE these records to bring them into PARTITIONs which are in near future. (all current data are inserted with PARTITIONKEY( a date) 6 month in future.

b) SPLIT MAX partition into a PARTITION that will contain all data of MAX partition and a New empty MAX partition.
THis should be fast due to Oracle's FAST SPLIT optimization.

c) SPLIT MAX into future dated partitions so that all future traffic are inserted into Correct partition.

d) In future when Oracle Upgrade/ExaData is available
Online SPLIT the heavy partition created in step c.

I hope I am not trying to do something stupid.
Currently we are try to get a prod like db setup to be able to test this.

Your feedback is welcome.

Chris Saxon
January 27, 2021 - 11:13 am UTC

1. I'm not aware of Exadata features that will make this specific operation faster

2. You're basic strategy sounds good

After splitting the max partition so it's empty, I'd look into switching to interval partitioning to avoid this problem happening again.

To do this, drop the max partition and set the interval:

alter table t drop partition pmax;
alter table t partition by interval ( ... );

More to Explore


Need more information on Administration? Check out the Administrators guide for the Oracle Database