Ensuring the next number returned from a sequence matches the current value from the table appears to be a common problem, my thoughts are described here. The biggest trouble is linking up the sequence to the column so we could automate the process.
TL;DRWe can now execute an ALTER statement to reset/align the sequence to a value appropriate to the column
alter table my_table modify (id generated as identity START WITH LIMIT VALUE);
The next insert will be problem free, and we don't need to do anything else.
The Underlying SequenceIdentity columns use a sequence under the hood, as hinted in the statement diagrams for the create table syntax.
|I love these diagrams|
I thought perhaps I could use my old technique on these sequences, but I forgot where to look for the name of the sequence associated with the identity column.
As usual Tim Hall pointed me in the right direction
select table_name, column_name, generation_type, sequence_name from all_tab_identity_cols where table_name = 'MY_TABLE' TABLE_NAME COLUMN_NAME GENERATION_TYPE SEQUENCE_NAME ------------- ----------- --------------- ------------- MY_TABLE ID BY DEFAULT ISEQ$$_430382
I was reminded shortly after that you can also see the data default referenced within SQL Developer
|SQL Developer Table properties|
However it turns out if you try to apply
alter sequenceto those generated by the system from the table DDL, you get the following error.
ORA-32793: cannot alter a system-generated sequence
So to increment the sequence beyond the most recent ID, I could make a bunch of requests to the next value of the sequence.
declare l_val pls_integer; begin for i in 1..240 -- use the difference between .nextval and max(id) loop l_val := ISEQ$$_430382.nextval; -- change to sequence returned from all_tab_identity_cols end loop; end; /This might be considered un-elegant, a dirty way to fix the problem. We're on 12c, surely there's a better way.
12c solutionThe sequence creation is built into the DDL, so why not maintenance? Check out the help for the ALTER TABLE command.
Now illustrate this in action.
LIMIT VALUE, which is specific to
, can only be used with
MODIFY. If you specify
LIMIT VALUE, then Oracle Database locks the table and finds the maximum identity column value in the table (for increasing sequences) or the minimum identity column value (for decreasing sequences) and assigns the value as the sequence generator's high water mark. The next value returned by the sequence generator will be the high water mark +
for increasing sequences, or the high water mark -
for decreasing sequences.
drop table seq_reset_test; create table seq_reset_test -- basic table with my suggested identity column options (id number generated by default on null as identity ,CONSTRAINT seq_reset_test_pk PRIMARY KEY (id) ,label varchar2(20) ); Table SEQ_RESET_TEST created. -- Will be given first ID of 1 insert into seq_reset_test (label) values ('Initial'); 1 row inserted. -- Simulate update of db without synchronising sequence update seq_reset_test set id = 1000 where id = 1; 1 row updated. -- ID too high for sequence select * from seq_reset_test; ID LABEL ---------- -------------------- 1000 Initial -- Find sequence name from all_tab_identity_cols select ISEQ$$_404558.currval from dual; CURRVAL ---------- 1 -- This will use ID 2, risking PK violation insert into seq_reset_test (label) values ('Second'); 1 row inserted. -- Magic alter statement alter table seq_reset_test modify (id generated by default on null as identity start with limit value); Table SEQ_RESET_TEST altered. -- This will use the updated sequence, avoiding max(id) value insert into seq_reset_test (label) values ('Third'); 1 row inserted. -- Proof select * from seq_reset_test; ID LABEL ---------- -------------------- 1000 Initial 2 Second 1001 Third select ISEQ$$_404558.currval from dual; CURRVAL ---------- 1001
So it seems we can throw away that old reset_seq.sql file?
Particularly if the replacement is now an ALTER TABLE command that doesn't need to know about any values.
alter table my_table modify (id generated /*by default on null*/ as identity START WITH LIMIT VALUE);
In comments the optional definition settings I find most useful, which would have been defined within the table DDL.
If Oracle can manage by IDs with sequences and do all the grunt work for me, go right ahead.