jcon.no: Oracle blog

I wrote a short little PLSQL to fix an out-of-sync sequence issue. For some unknown reasons, there was stored primary key values in the database which was way larger than the currval for the dedicated sequence.

Test case:

SQL> drop table big_table;

Table BIG_TABLE dropped.

SQL> drop sequence big_table_seq;

Sequence BIG_TABLE_SEQ dropped.

SQL> create table big_table as select * from all_objects where rownum<=10;

Table BIG_TABLE created.

SQL> update big_table set object_id=rownum;

10 rows updated.

SQL> alter table big_table add constraint big_table_pk primary key (object_id);

Table BIG_TABLE altered.

SQL> create sequence big_table_seq start with 1 increment by 1 cache 100;

Sequence BIG_TABLE_SEQ created.

SQL> INSERT INTO big_table (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
  2                         DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,
  3                         TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,
  4                         NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED) 
  5     VALUES ('LASSE','LASSE',null,big_table_seq.nextval,
  6             null,'TEST',to_date('07.07.2014','DD.MM.RRRR'),to_date('07.07.2014','DD.MM.RRRR'),
  7             '2014-07-07:05:39:03','TEST','N','N','N',
  8             '64','NONE',null,'Y','N');

Error starting at line : 11 in command -
INSERT INTO big_table (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
                       DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,
                       TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,
                       NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED) 
   VALUES ('LASSE','LASSE',null,big_table_seq.nextval,
           null,'TEST',to_date('07.07.2014','DD.MM.RRRR'),to_date('07.07.2014','DD.MM.RRRR'),
           '2014-07-07:05:39:03','TEST','N','N','N',
           '64','NONE',null,'Y','N')
Error report -
ORA-00001: brudd på unik skranke (LJ.BIG_TABLE_PK)

As we see the sequence is out-of-sync, as there is already stored values higher than the sequence nextval.
The following PL/SQL anonymous block fixed the issue:

set serveroutput on size 1000000
DECLARE
-------------------------------------------------
-- Description
-- Anonymous PLSQL Block to correct sequence
-------------------------------------------------

--------------------------------
-- WARNING! Set variables below
--------------------------------
   l_seq_name        VARCHAR2(30)   := 'BIG_TABLE_SEQ';
   l_tbl_name        VARCHAR2(30)   := 'BIG_TABLE';
   l_tbl_pk_col      VARCHAR2(30)   := 'OBJECT_ID';

   l_seq_current     NUMBER;
   l_seq_max         NUMBER;
   l_dummy           NUMBER;
   
   l_sql             VARCHAR2(4000);
   
   procedure run_sql(sql_i varchar2) as
   begin
      l_sql := sql_i;
      EXECUTE IMMEDIATE l_sql;
      dbms_output.put_line('SQL: ' || sql_i);
   end;
   procedure run_sql(sql_i varchar2, value_o OUT number) as
   begin
      l_sql := sql_i;
      EXECUTE IMMEDIATE l_sql INTO value_o;
      dbms_output.put_line('SQL: ' || sql_i);
   end;
BEGIN
   -- Get maxvalue
   run_sql('SELECT max(' || l_tbl_pk_col || ') FROM ' || l_tbl_name, l_seq_max);
   
   -- Get sequence currval
   run_sql('SELECT ' || l_seq_name || '.nextval FROM dual',l_seq_current);
   
   -- Adjust sequence
   run_sql('ALTER SEQUENCE ' || l_seq_name || ' INCREMENT BY ' || (l_seq_max - l_seq_current));
   run_sql('SELECT ' || l_seq_name || '.nextval FROM dual', l_dummy);
   run_sql('ALTER SEQUENCE ' || l_seq_name || ' INCREMENT BY 1');
EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line('ERROR: ' || l_sql);
END;
/

The output when running the PLSQL block:

SQL: SELECT max(OBJECT_ID) FROM BIG_TABLE
SQL: SELECT BIG_TABLE_SEQ.nextval FROM dual
SQL: ALTER SEQUENCE BIG_TABLE_SEQ INCREMENT BY 8
SQL: SELECT BIG_TABLE_SEQ.nextval FROM dual
SQL: ALTER SEQUENCE BIG_TABLE_SEQ INCREMENT BY 1

PL/SQL procedure successfully completed.

Now we can run the insert again:

SQL> INSERT INTO big_table (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
  2                         DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,
  3                         TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,
  4                         NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED) 
  5     VALUES ('LASSE','LASSE',null,big_table_seq.nextval,
  6             null,'TEST',to_date('07.07.2014','DD.MM.RRRR'),to_date('07.07.2014','DD.MM.RRRR'),
  7             '2014-07-07:05:39:03','TEST','N','N','N',
  8             '64','NONE',null,'Y','N');

1 row inserted.