Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Copy Sequences

Copy Sequences

Copy Sequences

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.

RE: Copy Sequences

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;

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.
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


Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Copy Sequences

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.

RE: Copy Sequences

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

Lead Application Developer
New York State, USA

RE: Copy Sequences

Even better, Bill! Well done.

RE: Copy Sequences

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:

SELECT owner, table_name
WHERE MAX(table_name.PKField) + 1 < Sequence.NextVal
ORDER BY table_name

---- Andy

There is a great need for a sarcasm font.

RE: Copy Sequences

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.

Lead Application Developer
New York State, USA

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close