Link to home
Create AccountLog in
Avatar of dba_damion
dba_damionFlag for Afghanistan

asked on

Oracle sequence number

There is sequence number that got out of synch I am not sure how database will allow this but it happened (ie sequence number went from 50001 to 45000 back to 50002). This is only for one day . I am thinking of updating the row id directly because there is series of reports that use the row id. This will not cause duplication . I want to know the risk of doing this
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dba_damion

ASKER

My question now is if i should directly update the rowid verse changing 15 reports. Are there any gatch us
Avatar of Sean Stuber
Sean Stuber

as stated above...

It's not recommended practice to modify primary keys,  but if you have a row with 45000 that is supposed to be someother number, then go ahead.  The only problem will be if you have foreign keys in which case you may need to disable the fk constraints, update the parent and children with the new id and then reenable the constraints

if you do disable/enable  to do the update across keys,  then you will need to ensure no other transactions are happening at the same time that might violate the parent/child keys when they are enabled.
>> ... disable the fk constraints ...

or insert new rows with the correct value then delete the 45000 rows?

I don't like disabling constraints on an active database.  To many chances of bad data sneaking in.
if the id doesnt have any significance in your business logic, you can change the id.

The best thing will be to consume one id on SQL prompt using sequence_name.nextval and then use that id. This will make sure that you will not hit the unique constraint violation in future.
and, as stated in http:#34473587 


if you application is expecting specific values from a sequence then you may have other issues and the hiccup you saw will likely happen again.
I will let you know how it went . I have never done this before . I feel like im sitting on pins hope this works .It let me update it. I hope there arent any problems I tested this theory with 3 records there is a total of 50
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.