Best way to reset an Oracle sequence to the next value in an existing column?

sql oracle oracle10g primary-key sequence
Cade Roux picture Cade Roux · May 23, 2011 · Viewed 141.3k times · Source

For some reason, people in the past have inserted data without using sequence.NEXTVAL. So when I go to use sequence.NEXTVAL in order to populate a table, I get a PK violation, since that number is already in use in the table.

How can I update the next value so that it is usable? Right now, I'm just inserting over and over until it's successful (INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)), and that syncs up the nextval.

Answer

Basanth Roy picture Basanth Roy · May 23, 2011

You can temporarily increase the cache size and do one dummy select and then reset the cache size back to 1. So for example

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;

Related questions

CASE .. WHEN expression in Oracle SQL

I have the table with 1 column and has following data Status a1 i t a2 a3 I want to display the following result in my select query Status| STATUSTEXT a1 | Active i | Inactive t | Terminated a2 | Active a3 | Active One …

Read More
How to insert date values into table

How can I insert into table with different input using / ,with date datatype? insert into run(id,name,dob)values(&id,'&name',[what should I write here?]); I'm using oracle 10g.

Read More
ORA-01861: literal does not match format string

When I try to execute this snippet: cmd.CommandText = "SELECT alarm_id,definition_description,element_id, TO_CHAR (alarm_datetime, 'YYYY-MM-DD HH24:MI:SS'),severity, problem_text,status FROM aircom.alarms WHERE status = 1 and TO_DATE (alarm_datetime,'DD.MM.…

Read More