Updating out of sync sequences

[Running Bear]

I have a live server which replicates data to a back up, occassionally I have to take the live server down and make the back up the live. When I do this my sequences are out of sync. Currently I have to manually update them.

As follows -

SELECT max(myPk) FROM myTbl
make a note of the val = X

DROP SEQUENCE myPK;
CREATE SEQUENCE myPK INCREMENT BY 1 START WITH X MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
commit;

I would like to write a pl/sql procedure to do this on the fly, is it possible and has anyone done this already?

I realise that one way round this problem would be to have a trigger on each table that increments and assigns the pk val on insert. It means altering my app. which will tak some time but will do it if it's the only way. Is this a good approach?

Any help and or guidance will be gratefully received

Cheers Al

[tamilselvan]

Before you shutdown the db, put the sequence in nocache option.
The command is:
alter sequence sequencename nocache.

This will gurantee the out of sync in sequences next time you start up the db.

Tamil

[Running Bear]

Tamil,

Thanks for that, I'll give it a go.

Regards Al