The error is pretty self explanatory and I even know where the issue is coming from. What I don't know is how to correct the problem.
We have an application that uses Oracle Lite to synchronize data on our users' computers with a master Oracle database. The application and synchronization was built long before I arrived at the company and I admit that I have very little understanding of how the Oracle Lite replication process works.
A couple of the tables in our database have ID numbers that are created by a sequence (named GUID_SEQ). That one sequence provides the ID numbers for several different tables. What little bit that I know about Oracle Lite's replication process is that it sets aside a batch of numbers for each user to use for the sequence so that there aren't multiple people attempting to use the same number.
I have a user who is now reporting that he is getting the POL-3220 error when attempting to create a new record in one of the tables using the sequential ID numbers. I looked into his local Oracle Lite table and found that he was allocated the number 103002 thru 103502 and his current value is 103044. I checked the server side table and found that all numbers between 103002 and 13502 already exist. (I'm not a genius, but I think this is why he's getting the error).
My question is, how can I reset the "batch" of numbers that the user is assigned so that he can get past this error?
one idea that comes into my mind(i dont know if you 'll like it) is to open the msql utility on the client .this is the utility that you can use to run sql queries. if he doesnt have it there is a tool.blablabla.cab file (if the client is windows mobile). then run the
SELECT MY_SEQUENCE.NEXTVAL FROM DUAL as many times as you need. its time the sequence is increased by one. run it so you get passed the number problem you have and then sync so that the client gets a new window of numbers
While the suggestion was not elegant, it was productive. I ran a test on my installation of the application and ran the sequence until I was out of numbers in my little batch. When I attempted to replicate with the server, I got an error POL-3264: Timeout when waiting for a lock table. To get past this error, I "de-installed" oracle lite from my machine then ran the scripts to re-install the database. Afterwords, I was able to sync properly and I found that I had a new batch of sequence numbers.
The problem is, that I've been given a batch of numbers that has already been used. I've attempted to "use" all the values in the batch several times and then re-sync, but each time the replication process sets me up with the same set of numbers. My batch is 103502 thru 104001. A simple query of the database shows that all the numbers in that range have been used.
Is there any way to manually manipulate the sequence reset a batch. What would happen if I change the minimum value of the sequence to (for example) 120000?
the timeout when waiting for a lock was a problem we encountered many times on the windows mobile. we had seen that it sometimes happens when connection drops during a sync and then you try syncing from start. then you got the error. we could manuall force a full refresh by deleting the temp files that olite stored in orace file. i would like to suggest you that you should have all the latest patches (if we are talking about 10gr3 ver) since many bugs have been fixed. we in our company have found 3 serious bugs ourselves and all have been fixed and included in the patches.
now , if you put the sequence in a higher starting value you "should" be able to force it to start giving numbers from that point and onwards. BUT although i love olite i do admit that sometimes things might go wrong. for instance it wouldnt suprise me if you then would be forced to fully unistall-reinstall the clients olite because you would get a message saying something about the sequence not being able to apply on the the client.
in my opinion, your idea is a good one, but try it first in your development environment before the production. oncerning the fact that the numbers have already been used i do admit that it is kinda weird because we never run into this issue. olite always gave diffrent number windows for each client , not once did olite gave the same window to 2 clients. therefore having the same primary key never occured.
please make sure you are not using some other kind of primary key generation in parallel (something you wrote yourself, like storing the value on a table and increasing it).
by the way i would bed that you can find the next value of the sequence if you check the tables in the mobileadmin schema in the database and try to find a table indicating sequences (havent searched it my self but i believe it is stored there because , olite works this way)