Easy Sequence Value Reset

I've found that there are times when an Oracle sequence has either been advanced just short of or well beyond its intended value that would be more accurately aligned to its associated primary key. In common practice, the former scenario would likely produce an ORA-00001 error, i.e. unique constraint violation, when an attempt to add a new record with a primary key value fetched from a sequence conflicts with a value found in the key's index. However, for the latter situation when the next value of the sequence is well beyond the expected value, it seems like more developers than DBAs that I know have been bothered by inexplicable gaps between the maximum primary key value and a much higher value fetched from its serving sequence. I have found that the concern that these developers have expressed is well founded in certain circumstances. For example, what if that particular sequence was serving primary key values for an access audit table? A table like that with a heavy volume of insert-only activity that doesn't get a lot of attention until something like a major security incident occurs should greatly concern every developer and DBA if they saw large gaps (if any really) between the primary key values of those records. For all they know, a hacker may have covered their tracks by deleting a large number of audit records where those key value gaps exist.

So, how should the next sequence value be adjusted accordingly? The sequence object could be dropped and recreated with an adjusted minimum value, but this option would invalidate any dependent source (triggers, packages, functions, procedures, etc.) and also require any privileges from the old sequence to be applied to the new sequence. All of those steps introduce a degree of unwarranted risk compared to the script that I've developed just for these occasions.

In a nutshell, the script temporarily alters the "maximum value", "cycle", and "increment" properties of the sequence to wrap the next value fetched to the desired value. This script takes three parameters: 1) the desired sequence value, 2) the sequence name, and 3) the sequence owner which defaults to the connected user if omitted or null. Note that the minimum value is never altered and the maximum value allowed by the sequence must be greater than the desired value before execution.

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
 lWorkingValue NUMBER;
 lDesiredValue CONSTANT NUMBER := &1;

 lSequenceName       CONSTANT VARCHAR2(30) := UPPER('&2');
 lSequenceOwner      CONSTANT VARCHAR2(30) := UPPER(NVL('&3', USER));
 lSequenceProperties ALL_SEQUENCES%ROWTYPE;
BEGIN
 -- capture the current sequence properties
 SELECT *
 INTO lSequenceProperties
 FROM ALL_SEQUENCES
 WHERE SEQUENCE_OWNER=lSequenceOwner
   AND SEQUENCE_NAME=lSequenceName;

 -- make sure the the increment is set to 1
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' INCREMENT BY 1';

 -- make sure the sequence is allowed to wrap
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' CYCLE';

 -- fetch a value from the sequence
 EXECUTE IMMEDIATE 'SELECT '|| lSequenceOwner || '.' || lSequenceName ||'.NEXTVAL FROM DUAL'
 INTO lWorkingValue;

 -- set the max value of the sequence to the last value fetched
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' MAXVALUE '|| lWorkingValue;

 -- fetch the next value to wrap the sequence which will reset it to the minimum value
 EXECUTE IMMEDIATE 'SELECT '|| lSequenceOwner || '.' || lSequenceName ||'.NEXTVAL FROM DUAL'
 INTO lWorkingValue;

 -- reset the original max value
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' MAXVALUE '|| lSequenceProperties.MAX_VALUE;

 -- if the desired value is in fact the sequence minimum value, 
 -- then the sequence should be at the desired value at this point
 -- since the sequence wrapped (or cycled) with the last fetch;
 -- otherwise, advance the sequence with a single fetch by first changing the 
 -- increment-by value to the difference between the desired value and the
 -- sequence minimum value
 IF (lDesiredValue <> lSequenceProperties.MIN_VALUE) THEN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' INCREMENT BY ' || TO_CHAR(lDesiredValue - lSequenceProperties.MIN_VALUE);

  -- fetch a value from the sequence to set the sequence to the desired value
  EXECUTE IMMEDIATE 'SELECT '|| lSequenceOwner || '.' || lSequenceName ||'.NEXTVAL FROM DUAL'
  INTO lWorkingValue;
 END IF;

 -- reset the original increment value
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' INCREMENT BY ' || lSequenceProperties.INCREMENT_BY;

 -- reset the original cycle flag
 IF (lSequenceProperties.CYCLE_FLAG='N') THEN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' NOCYCLE';
 END IF;

 DBMS_OUTPUT.PUT_LINE('Last value fetched from the ' || lSequenceOwner || '.' || lSequenceName || ' sequence was ' || lWorkingValue);
END;
/

This version of the script below has been very useful in the lab environments where Flashback Database is being used to reset test data. Sequences are not affected by flashback operations and need to be reset to the subsequent values of their associated primary keys manually or, as demonstrated here, programmatically. So, three new parameters have been added: 1) the table name, 2) the column name of the primary key, and 3) the table owner which defaults to the current user if omitted or null. Note that this script will derive the desired sequence value from the highest value in the primary key column, so the desired value parameter has been omitted here. I've highlighted the main edit to this script which is just after the BEGIN statement.


SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
 lWorkingValue NUMBER;
 lDesiredValue NUMBER;

 lSequenceName       CONSTANT VARCHAR2(30) := '&1';
 lSequenceOwner      CONSTANT VARCHAR2(30) := NVL('&2', USER);
 lSequenceProperties ALL_SEQUENCES%ROWTYPE;

 lTableName          CONSTANT VARCHAR2(30) := '&3';
 lColumnName         CONSTANT VARCHAR2(30) := '&4';
 lTableOwner         CONSTANT VARCHAR2(30) := NVL('&5', USER);
BEGIN
 -- get the desired value of the sequence which should be the highest value found in the primary key column
 EXECUTE IMMEDIATE 'SELECT MAX(' || lColumnName || ') FROM ' || lTableOwner || '.' || lTableName
 INTO lDesiredValue; 

 -- capture the current sequence properties
 SELECT *
 INTO lSequenceProperties
 FROM ALL_SEQUENCES
 WHERE SEQUENCE_OWNER=lSequenceOwner
   AND SEQUENCE_NAME=lSequenceName;

 -- make sure the the increment is set to 1
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' INCREMENT BY 1';

 -- make sure the sequence is allowed to wrap
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' CYCLE';

 -- fetch a value from the sequence
 EXECUTE IMMEDIATE 'SELECT '|| lSequenceOwner || '.' || lSequenceName ||'.NEXTVAL FROM DUAL'
 INTO lWorkingValue;

 -- set the max value of the sequence to the last value fetched
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' MAXVALUE '|| lWorkingValue;

 -- fetch the next value to wrap the sequence which will reset it to the minimum value
 EXECUTE IMMEDIATE 'SELECT '|| lSequenceOwner || '.' || lSequenceName ||'.NEXTVAL FROM DUAL'
 INTO lWorkingValue;

 -- reset the original max value
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' MAXVALUE '|| lSequenceProperties.MAX_VALUE;

 -- if the desired value is in fact the sequence minimum value, 
 -- then the sequence should be at the desired value at this point
 -- since the sequence wrapped (or cycled) with the last fetch;
 -- otherwise, advance the sequence with a single fetch by first changing the 
 -- increment-by value to the difference between the desired value and the
 -- sequence minimum value
 IF (lDesiredValue <> lSequenceProperties.MIN_VALUE) THEN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' INCREMENT BY ' || TO_CHAR(lDesiredValue - lSequenceProperties.MIN_VALUE);

  -- fetch a value from the sequence to set the sequence to the desired value
  EXECUTE IMMEDIATE 'SELECT '|| lSequenceOwner || '.' || lSequenceName ||'.NEXTVAL FROM DUAL'
  INTO lWorkingValue;
 END IF;

 -- reset the original increment value
 EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' INCREMENT BY ' || lSequenceProperties.INCREMENT_BY;

 -- reset the original cycle flag
 IF (lSequenceProperties.CYCLE_FLAG='N') THEN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| lSequenceOwner || '.' || lSequenceName ||' NOCYCLE';
 END IF;

 DBMS_OUTPUT.PUT_LINE('Last value fetched from the ' || lSequenceOwner || '.' || lSequenceName || ' sequence was ' || lWorkingValue);
END;
/