Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Alter a Sequence

407048Jul 23 2007 — edited Jul 23 2007
Suppose I have the following sequences(in database 1) with their nextval as :
select emp_seq.nexval from dual;
4944
select dept_seq.nextval from dual;
30

emp_seq is used for the primary key empid in employee table. Similarly dept_seq is used for the primary key deptid in department table.
Now suppose, I do an export of these tables from another database(Say database2) having very large data, and import it here, and suppose there are 10000 and 100 rows respectively in the emp and dept tables,
From database2, if I use the above sequeneces, it complains that 4944 and 30 already found and fails with unique key constraint.

Is there a script that I can run which alters the sequence to the most appropriate value based on the value in the table ?
(I can always change the INCREMENT BY to a larger value and then do a nextval and change the INCREMENT BY back to 1, but I am looking for a script to do this )

In the above example, emp_seq.nextval should be 10001 and dept.nextval should be at 101 based on the values in the table. Moreover, I am not looking at an option to drop and recreate the sequence as I am not interested in losing the grants for now.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 20 2007
Added on Jul 23 2007
2 comments
205 views