If Oracle sequence errors are occurring for several Clarity tables can the correction be done for all tables with one procedure?

Document ID : KB000053102
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

When unique constraint errors occur due to sequence number being out of sequence, this is corrected by incrementing the sequence value so that the value is higher than the max(id).

Solution:

The following Oracle only script will increment the sequence number for all the sequences. Typically when multiple tables are short in sequence it is due to db backups and restores on non-production environments. For such environments this script should be sufficient.

Note: On production environments it is recommended to individually update sequences.

DECLARE

v_val NUMBER;
v_sqlstr VARCHAR2(1000);

CURSOR SequenceNameCursor IS
SELECT SEQUENCE_NAME
FROM ALL_SEQUENCES
WHERE SEQUENCE_OWNER = 'NIKU';

BEGIN

FOR c_rec in SequenceNameCursor LOOP
v_sqlstr := 'ALTER SEQUENCE NIKU.' || c_rec.sequence_name ||'
INCREMENT BY 100';
dbms_output.put_line(v_sqlstr);

v_sqlstr := 'SELECT NIKU.' || c_rec.sequence_name || '.NEXTVAL FROM DUAL';
dbms_output.put_line(v_sqlstr);
dbms_output.put_line(v_sqlstr);

v_sqlstr := 'ALTER SEQUENCE NIKU.' || c_rec.sequence_name ||'
INCREMENT BY 1';
dbms_output.put_line(v_sqlstr);
END LOOP;
dbms_output.put_line(';');
END;
/