Home » SQL & PL/SQL » SQL & PL/SQL » oracle sequence
oracle sequence [message #349247] Fri, 19 September 2008 08:35 Go to next message
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 #349248 is a reply to message #349247] Fri, 19 September 2008 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so my question does database makes a log of when a sequence was reset if somebody reset that manually can we find that ?

No, you can:
- check last_ddl_time from dba_objects
- use Log Miner to search into archived or online redo logs.

Quote:
how can I find the reason for sequence to give lesser value

DROP/CREATE or ALTER sequence.
Also a bunch of nextval if you set "cycle".

Regards
Michel
Re: oracle sequence [message #349390 is a reply to message #349247] Sun, 21 September 2008 02:29 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous message
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! Embarassed

Regards
Harry
Previous Topic: ORA 06550 cannot execute procedure
Next Topic: group by issue with adding another field
Goto Forum:
  


Current Time: Fri Dec 09 21:22:51 CST 2016

Total time taken to generate the page: 0.23738 seconds