Running Bear
10-15-2003, 05:43 AM
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
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