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.
Post a Comment