oracle sequence [message #349247] |
Fri, 19 September 2008 08:35  |
miroconnect@yahoo.com
Messages: 202 Registered: April 2006
|
Senior Member |
|
|
Yesterday we deployed our application to production , with some database updates ,and surprisingly most of our sequnces are causing problems. Any insert we try to make I was getting unique constraint PK violated and on further investigation we found that sequence.nextval returns a values lesser than max Pk from table , so my question does database makes a log of when a sequence was reset if somebody reset that manually can we find that ? or any other action which can automatically casue a sequnece to reset please help me how can I find the reason for sequence to give lesser value
|
|
|
|
Re: oracle sequence [message #349390 is a reply to message #349247] |
Sun, 21 September 2008 02:29   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
miroconnect@yahoo.com wrote on Fri, 19 September 2008 15:35 | please help me how can I find the reason for sequence to give lesser value
|
Since you are looking for possible causes, here are some:
- conversion/migration was part of deployment, during which records were inserted that got their values from another source than the intended sequence
- during deployment some sequences were recreated because (part of) schema-creation scripts were run
If your sequence-name is logically mappable to the column it is used for (e.g. sequence-name contains table alias, and PK-name also contains table alias), then you can quite easily write a script that automatically fetches sequence-numbers until all sequences are up-to-date.
|
|
|
Re: oracle sequence [message #349414 is a reply to message #349390] |
Sun, 21 September 2008 11:26  |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Franks examination provides an effective remedy as I have been hit with this scenario during conversions. A fix script to fetch
the nextval of sequence to update to the correct value to me
was the quickest solution.
I cannot think of any other reasons than Franks for the problem either.
I ponder however, if things can get out of sync with processes using the same sequence concurrently where vals are buffered and
one process crashes...mental note for myself to read more on
sequences!
Regards
Harry
|
|
|