Alter a Sequence
407048Jul 23 2007 — edited Jul 23 2007Suppose 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.