Synchronise Sequence value with 12c Identity Column

My journey into 12c continues with the use of identity columns, this time regarding data that had been imported from another database, but sequences haven't been updated.

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;DR

We 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 Sequence

Identity 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 sequence to those generated by the system from the table DDL, you get the following error.
ORA-32793: cannot alter a system-generated sequence
Fancy that.

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 solution

The sequence creation is built into the DDL, so why not maintenance? Check out the help for the ALTER TABLE command.

START WITH LIMIT VALUE, which is specific to identity_options, can only be used with ALTER TABLE MODIFY. If you specify START WITH 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 + INCREMENT BY integer for increasing sequences, or the high water mark - INCREMENT BY integer for decreasing sequences.

Now illustrate this in action.

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

Conclusion

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.

Related Posts

12 Column Upgrades
Decommissioning Triggers in 12c