ORA-14099: all rows in table do not qualify for specified partition

We have a table within our DB which stores every transaction which is made within our application. As you can imagine, this table is heavily updated and we create around 10,000,000 rows everyday on our Production system. Further to this, we are required to save this data for 2 weeks just in case we need to resolve issues and want to identify any timing isues with specific components. It’s very useful when we release new code to see the before and after times of how long actions take.

Every night there we also summarise this data so that we can store historical information but a slimmed down version. At the same time as doing the summarisation, we take the opportunity to archive off the full day’s worth of data so that when the next day comes around we have a clean table to insert into. The method we use for this process is to exchange the partition.

We accomplish this task in a few steps:

1. Find out the maximum value for the tmstamp in the LOGGING table
2. Add 1 second to that value to give the HIGH VALUE for the LOGGING ARCHIVE partitioned table
3. Alter the LOGGING ARCHIVE table and add a partition with the HIGH VALUE calculated in step 2
4. Execute the exchange partitions command for the LOGGING and LOGGING ARCHIVE tables

Now, all the way through this process there are writes out to a different logging table which is used to track what actions are taking place as part of the archiving process. We log here so that we can see how long stages took to complete, keep a historical log of what events took place when, and debug more easily when issues arise. These are just simple statements which insert information into a small logging table, and also COMMIT those changes as they happen. And therein lies the problem…

When you have locked a table in EXCLUSIVE mode, any COMMIT or ROLLBACK command from within the same session will release that EXCLUSIVE lock! So, while we thought that the tables were still locked for the duration of the exchange that was simply not the case. I found this out the hard way last night, when a QA run of a change to the procedure produced this error:

ORA-14099: all rows in table do not qualify for specified partition

Our QA application was very busy at the time and actions were running through it when I scheduled the job to run, so it failed because there were actions in the LOGGING table which had a greater timestamp value than the HIGH VALUE column of the LOGGING ARCHIVE partitioned table with which we were attempting to exchange partitions.

The Solution…

I removed any commit statements from the procedure between the locking of the tables and the releasing of the locks, which is actually just the exchanging of the partitions action.

The Solution 2…

I will be re-writing the procedure to use a different method for archiving/partitioning off the data from the main transaction table. I am looking at using partition splitting, and always maintaining a few days in advance. A partition split operation on an empty MAXVALUE partition where no data is required to be sorted into different partitions is, apparently, equivalent in terms of performance as an ADD PARTITION statement. Also, I use this method for many other partitioning jobs in the system so I am familiar with how it works.

Test It…

To test the claim about commits and rollbacks while in EXCLUSIVE MODE simply test it:

Session 1:

-- Create table and grant privs on it to a user
SQL> create table exclusive_test (id number(10));
Table created.
SQL> grant all on exclusive_test to testuser;
Grant succeeded.

Session 2:

-- Test that the insert works normally first
SQL> insert into sys.rj_testing values (1);
1 row created.
SQL> commit;
Commit complete.
Session 1:
-- Now lock the tables
SQL> lock table exclusive_test in exclusive mode;
Table(s) Locked.
Session 2:
-- Attempt to re-run the insert command
SQL> insert into sys.rj_testing values (1);
-- You will see that the command just hangs
Session 1:
-- Type commit in the session and watch session 2 also insert into the table after you have committed
SQL> commit;
Commit complete.

Other Notes…

There are downsides to running this way because there can be no actions going through the system for approximately 30 seconds while there are locks on the table so in hindsight this is not the best way to perform this propecia really work helps process. I will blame the person who wrote it originally who has now left the company…

Like it, share it...

Category: Partitioning

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *