This discussion is archived
4 Replies Latest reply: Dec 12, 2012 9:21 AM by rp0428 RSS

blocking locks encountered while splitting partitions in 11g

acrsny Newbie
Currently Being Moderated
Background: I've taken over administration of a database that has several date-range partitioned tables that, suffering from a lack of proper administration, have not had their MAXVALUE partition split into the requisite monthly partitions, in almost a year. As a result, in some cases the MAXVALUE partition has 10 million rows (the monthly partitions should average 750K rows).

My understanding is that the syntax I am using to perform the split, should allow for on-line access to the tables while the split is being performed. Here is my syntax without the actual table names:

ALTER TABLE owner.table_name
SPLIT PARTITION MAXVALUE AT
(TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
INTO (PARTITION PART201201
TABLESPACE tablespace
PCTFREE 10
INITRANS 1
MAXTRANS 255,
PARTITION MAXVALUE) update indexes;

Problem: In attempting to split the MAXVALUE partition into monthly partitions while the database is on-line and accessible, I see application sessions being blocked by my split session.

So, is my understanding above incorrect? Is there some additional syntax that I am missing? Or, are the blocking locks I'm seeing merely transient in nature, and can be ignored? The server I'm running on is not very "beefy", and the split is taking quite a while to run.

Any assistance would be appreciated.
  • 1. Re: blocking locks encountered while splitting partitions in 11g
    Max Seleznev Explorer
    Currently Being Moderated
    Partition split is a multi-step operation that creates two new partition segments, makes a switch (e.g. data dictionary operation) then drops an old partition segments. Locks of different strength are acquired during those step including dictionary locks and partition locks.
    The partition being split cannot have any DML operations going on, but other partitions of the same table can.

    Hope it helps.
  • 2. Re: blocking locks encountered while splitting partitions in 11g
    rp0428 Guru
    Currently Being Moderated
    >
    Background: I've taken over administration of a database that has several date-range partitioned tables that, suffering from a lack of proper administration, have not had their MAXVALUE partition split into the requisite monthly partitions, in almost a year. As a result, in some cases the MAXVALUE partition has 10 million rows (the monthly partitions should average 750K rows).

    My understanding is that the syntax I am using to perform the split, should allow for on-line access to the tables while the split is being performed.
    . . .
    Problem: In attempting to split the MAXVALUE partition into monthly partitions while the database is on-line and accessible, I see application sessions being blocked by my split session.

    So, is my understanding above incorrect? Is there some additional syntax that I am missing? Or, are the blocking locks I'm seeing merely transient in nature, and can be ignored? The server I'm running on is not very "beefy", and the split is taking quite a while to run.
    >
    DML that modifies the table cannot be performed on the table while the split is ongoing.

    Your bigger problem is that you are using the WRONG method for your use case. If you repeatedly split the MAXVALUE partition you will be repeatedly copying and moving the same ultimate MAXVALUE data over and over again.

    DBMS_REDEFINITION is one of your options. But since I just answered this same question 3 days ago see my reply in this thread for a more thorough discussion of your options.
    Re: Which is the Best method to Split Partition
  • 3. Re: blocking locks encountered while splitting partitions in 11g
    acrsny Newbie
    Currently Being Moderated
    Yeah, THAT is my problem. The method of repeatedly spltting the MAVALUE partition is very nice as long as you can do it BEFORE you get a bunch of data in it.

    I appreciate your insights regarding redef. My only concern there, and perhaps it's not a valid concern, is that these tables are set up in STREAMS, so, I imagine I would need to, at the very least, stop streams while performing any redef (or, will streams handle it gracefully? Who knows?).

    In any case, I've decided to go a little less high-tech, and will copy the existing data out of the MAXVALUE partition, while at the same time capturing any new rows coming in (via a temporary AFTER INSERT trigger) into a holding table. Then, I'll truncate that MAXVALUE partition, split it appropriately, re-load it from the copy and the holding table, and hopefully, carry on. I've set up controls to prevent any loss of new rows, as well as any duplication of existing rows. The "insert-only" nature of this table allows me to do this. I don't think I'd be able to do it this way if the table allowed updates. It ain't pretty, but, it works, and fairly quickly too.

    Thanks again.
  • 4. Re: blocking locks encountered while splitting partitions in 11g
    rp0428 Guru
    Currently Being Moderated
    >
    In any case, I've decided to go a little less high-tech, and will copy the existing data out of the MAXVALUE partition, while at the same time capturing any new rows coming in (via a temporary AFTER INSERT trigger) into a holding table. Then, I'll truncate that MAXVALUE partition, split it appropriately, re-load it from the copy and the holding table, and hopefully, carry on.
    >
    Why not just use the method I outlined in the thread I referenced? Copy the data out to a partitioned table and then just swap the partitions back in.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points