Copy Sequences

[Andrzejek]

From time to time our DBA copies tables (data) from Production to Test environment – after we (developers) mess it up (Test).
DBA claims that the sequences cannot be copied and have to be re-set in Test. (weird in my opinion…)

Is there a way to copy Sequences from place to place, along with the tables?
And if not – is there a way to ensure the sequences’ Next Value is correct in respect to the last PK value in a table? Programmatically, not ‘by-hand’, of course.



---- Andy

There is a great need for a sarcasm font.

 

[fredericofonseca]

He is right in saying that they can't be copied.

And there is no direct way to change the sequence to another number, however there is a work around which would need to be coded by you guys.

Trick is to alter the sequence to go to a higher increment, and doing a single select from it - then resetting back to the standard increment

ALTER SEQUENCE mysequence INCREMENT BY 100; -- this 100 can be a variable
select mysequence.nextval from dual;
ALTER SEQUENCE mysequence INCREMENT BY 1;

So a small proc to determine what is the highest number for the tables associated with a particular sequence and then set it to that value + 1. Easy enough to do for a developer and could be a script to execute always after a restore from prod.
e.g.
max pk value = 8888
current sequence number 8000
- set increment to 8888-8000 , retrieve nextval, reset it back to 1

code similar to this one - used to copy from one instance to the other, but this may not be what you need or you may not have access to do it this way

https://stackoverflow.com/questions...-sequence-from-one-oracle-database-to-another

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[carp]

And if you need to get to a lower value, you can try resetting from nocycle to cycle, incrementing as suggested by Frederico, then altering back to nocycle.

 

[Beilstwh]

Carp said,
And if you need to get to a lower value, you can try resetting from nocycle to cycle, incrementing as suggested by Frederico, then altering back to nocycle.

Simply change the increment to a negative instead of a positive to change the value down



Bill
Lead Application Developer
New York State, USA

 

[carp]

Even better, Bill! Well done.

 

[Andrzejek]

I would like to have a piece of code to check if last PKs in tables match with the sequences for those tables. Fortunately, all sequences end with _SEQ in their names, if that’s any help.

Is there any code to do that?
Something like:
[tt]
SELECT owner, table_name
FROM ALL_TABLES
WHERE MAX(table_name.PKField) + 1 < Sequence.NextVal
ORDER BY table_name
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

There is no link between a table column and a sequence. Even if you know the table name, you would have had to use a naming standard for the column that is using the sequence. The only exception to this is if you are using an IDENTITY column. If you are and you look at the table ALL_TAB_COLUMNS, the column IDENTITY_COLUMN would be equal to "YES" and the column DATA_DEFAULT(long) will contain the name of the sequence that it is using.

Bill
Lead Application Developer
New York State, USA