How do I reset a sequence in Oracle?

Asked 9/9, 2008 at 9:36 Answered 18/11, 2019 at 22:3

205

In PostgreSQL, I can do something like this:

ALTER SEQUENCE serial RESTART WITH 0;

Is there an Oracle equivalent?

Ryeland answered 9/9, 2008 at 9:36 Comment(3)

Have a look at "Sequence resets" here. – Winkle 9/9, 2008 at 9:45

WARNING: all of the code below is only valid for sequences that were initially created with "increment by 1". If the original sequence was created with an increment != 1; after applying any of the above procedures, the increment will have changed to 1! The correct increment value to use can be obtained from the user_sequences view. – Vex 13/4, 2011 at 10:54

Drop and recreate the sequence – Verisimilitude 27/7, 2016 at 13:20

165

Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too.

[email protected]> 
create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                          ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/

From this page: Dynamic SQL to reset sequence value
Another good discussion is also here: How to reset sequences?

Influent answered 18/9, 2008 at 15:34 Comment(4)

@Dougman:hi 'm beginner....in the above answer why do u mention into clause in the last instead execute immediate 'select ' || p_seq_name || '.nextval INTO l_val from dual' ; – Loki 23/2, 2013 at 8:48

@Thiyagu: In PL/SQL this is the syntax when using execute immediate to capture the output of a select returning at most 1 row. Here is the documentation on execute immediate: docs.oracle.com/cd/B28359_01/appdev.111/b28370/… – Influent 26/2, 2013 at 21:5

@matra I don't see a scenario in which one would need to reset a sequence and be in a concurrent environment woth other users of the same sequence. – Belch 19/9, 2014 at 14:26

Why you need to select the sequence, why not just do the last line 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0'; – Ternion 15/12, 2018 at 12:17

125

A true restart is not possible AFAIK. (Please correct me if I'm wrong!).

However, if you want to set it to 0, you can just delete and recreate it.

If you want to set it to a specific value, you can set the INCREMENT to a negative value and get the next value.

That is, if your sequence is at 500, you can set it to 100 via

ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM dual;
ALTER SEQUENCE serial INCREMENT BY 1;

Loch answered 9/9, 2008 at 9:46 Comment(2)

Just a note for people in PLSQL. Be sure to add "limit 1;" or "rownum =1" to the select statement otherwise you can end up running nextVal a couple of times and incrementing by -400 however many times. – Taboret 6/3, 2014 at 21:58

Error sequence .NEXTVAL goes below MINVALUE and cannot be instantiated when INCREMENT BY -<<big_number>> – Mors 17/8, 2015 at 20:42

For regular sequences:

alter sequence serial restart start with 1;

For system-generated sequences used for identity columns:

alter table table_name modify id generated by default on null as identity(start with 1);

This feature was officially added in 18c but is unofficially available since 12.1.

It is arguably safe to use this undocumented feature in 12.1. Even though the syntax is not included in the official documentation, it is generated by the Oracle package DBMS_METADATA_DIFF. I've used it several times on production systems. However, I created an Oracle Service request and they verified that it's not a documentation bug, the feature is truly unsupported.

In 18c, the feature does not appear in the SQL Language Syntax, but is included in the Database Administrator's Guide.

Lauder answered 30/10, 2013 at 3:31 Comment(8)

Hey @Jon ,I am aware of the undocumented feature, however, I didn't know it is seen in the generated script from DBMS_METADATA_DIFF. Could you let me know how you generated the script, which procedure etc.? I would try to test it too. – Resplendence 8/5, 2015 at 9:38

@LalitKumarB I stumbled upon that feature while answering this question. – Lauder 8/5, 2015 at 13:23

I guess this was implemented to support "alter table your_table modify (id generated by default on null as identity start with limit value);". Nice to know it works on standard sequences, too! – Breastplate 12/1, 2017 at 14:47

In the case the minvalue of the sequennce is greater 0 consider writing ... RESTART START WITH 0 MINVALUE 0 – Ardis 28/4, 2019 at 11:26

@Ardis Good point. In most cases just START WITH 0 would throw an error. That's what the OP asked for, but since this question reaches a large audience I changed the value to 1 to work for most people. – Lauder 8/10, 2019 at 23:10

Info: this feature is working with Oracle DB 12.2 (12c) too. Great answer, thx! – Strung 12/8, 2020 at 13:25

doesn't seem to work with a system-generated sequence in 19c. Get error ORA-32793: cannot alter a system-generated sequence. – Siltstone 28/6, 2022 at 22:43

It seems that this way can be used without changing sequence's original minvalue to minvalue - 1. Without this way, the condition last_number == min_value can hold only when the sequence is created and hasn't yet generate nextval – If you have to do not change minvalue, you have to drop and create the sequence. – Hawkie 17/2, 2023 at 8:35

This is my approach:

drop the sequence
recreate it

Example:

--Drop sequence

DROP SEQUENCE MY_SEQ;

-- Create sequence 

create sequence MY_SEQ
minvalue 1
maxvalue 999999999999999999999
start with 1
increment by 1
cache 20;

Erine answered 19/5, 2010 at 14:56 Comment(2)

Just be aware that the drop will invalidate any objects that depend on that sequence and they will have to be recompiled. – Influent 23/12, 2011 at 18:57

You'll also have to re-grant any grants that were given to select from the sequence. – Gourmet 7/1, 2013 at 23:37

My approach is a teensy extension to Dougman's example.

Extensions are...

Pass in the seed value as a parameter. Why? I like to call the thing resetting the sequence back to the max ID used in some table. I end up calling this proc from another script which executes multiple calls for a whole bunch of sequences, resetting nextval back down to some level which is high enough to not cause primary key violations where I'm using the sequence's value for a unique identifier.

It also honors the previous minvalue. It may in fact push the next value ever higher if the desired p_val or existing minvalue are higher than the current or calculated next value.

Best of all, it can be called to reset to a specified value, and just wait until you see the wrapper "fix all my sequences" procedure at the end.

create or replace
procedure Reset_Sequence( p_seq_name in varchar2, p_val in number default 0)
is
  l_current number := 0;
  l_difference number := 0;
  l_minvalue user_sequences.min_value%type := 0;

begin

  select min_value
  into l_minvalue
  from user_sequences
  where sequence_name = p_seq_name;

  execute immediate
  'select ' || p_seq_name || '.nextval from dual' INTO l_current;

  if p_Val < l_minvalue then
    l_difference := l_minvalue - l_current;
  else
    l_difference := p_Val - l_current;
  end if;

  if l_difference = 0 then
    return;
  end if;

  execute immediate
    'alter sequence ' || p_seq_name || ' increment by ' || l_difference || 
       ' minvalue ' || l_minvalue;

  execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_difference;

  execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue ' || l_minvalue;
end Reset_Sequence;

That procedure is useful all by itself, but now let's add another one which calls it and specifies everything programmatically with a sequence naming convention and looking for the maximum value used in an existing table/field...

create or replace
procedure Reset_Sequence_to_Data(
  p_TableName varchar2,
  p_FieldName varchar2
)
is
  l_MaxUsed NUMBER;
BEGIN

  execute immediate
    'select coalesce(max(' || p_FieldName || '),0) from '|| p_TableName into l_MaxUsed;

  Reset_Sequence( p_TableName || '_' || p_Fieldname || '_SEQ', l_MaxUsed );

END Reset_Sequence_to_Data;

Now we're cooking with gas!

The procedure above will check for a field's max value in a table, builds a sequence name from the table/field pair and invokes "Reset_Sequence" with that sensed max value.

The final piece in this puzzle and the icing on the cake comes next...

create or replace
procedure Reset_All_Sequences
is
BEGIN

  Reset_Sequence_to_Data( 'ACTIVITYLOG', 'LOGID' );
  Reset_Sequence_to_Data( 'JOBSTATE', 'JOBID' );
  Reset_Sequence_to_Data( 'BATCH', 'BATCHID' );

END Reset_All_Sequences;

In my actual database there are around one hundred other sequences being reset through this mechanism, so there are 97 more calls to Reset_Sequence_to_Data in that procedure above.

Love it? Hate it? Indifferent?

Damask answered 19/5, 2011 at 17:35 Comment(3)

I love it. I would add a variable to get and save the increment by value from the user_sequences table. (It might not be 1). Note: might need to use the all_sequences table instead. In this case you might want to pass in the sequence_owner as well. – Nucleon 22/7, 2011 at 15:16

Can't upvote you enough. This is a pretty common problem when you deal with data migration, and this is the best approach AFAIK if you're stuck with sequences. – Fervent 27/11, 2012 at 16:59

Upvoted because this is an excellent approach. Only downside is that it can result in unpredictable behaviour in a RAC system, where l_current may be one of various values, depending on which node the script is run; re-running the script can result in different results. I found if I ran it multiple times it eventually settled on a particular value. – Megagamete 19/8, 2014 at 3:46

The following script set the sequence to a desired value:

Given a freshly created sequence named PCS_PROJ_KEY_SEQ and table PCS_PROJ:

BEGIN
   DECLARE
      PROJ_KEY_MAX       NUMBER := 0;
      PROJ_KEY_CURRVAL   NUMBER := 0;
   BEGIN

    SELECT MAX (PROJ_KEY) INTO PROJ_KEY_MAX FROM PCS_PROJ;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY ' || PROJ_KEY_MAX;
    SELECT PCS_PROJ_KEY_SEQ.NEXTVAL INTO PROJ_KEY_CURRVAL FROM DUAL;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY 1';

END;
END;
/

Cleliaclellan answered 4/11, 2010 at 18:53 Comment(1)

You forgot the minus in your first DDL statement (also, there's an extra END keyword). – Conservative 15/12, 2016 at 14:23

This stored procedure restarts my sequence:

Create or Replace Procedure Reset_Sequence  
  is
  SeqNbr Number;
begin
   /*  Reset Sequence 'seqXRef_RowID' to 0    */
   Execute Immediate 'Select seqXRef.nextval from dual ' Into SeqNbr;
   Execute Immediate 'Alter sequence  seqXRef increment by - ' || TO_CHAR(SeqNbr) ;
   Execute Immediate 'Select seqXRef.nextval from dual ' Into SeqNbr;
   Execute Immediate 'Alter sequence  seqXRef increment by 1';
END;

Ellora answered 29/7, 2009 at 17:23 Comment(1)

+1 - You could also parameterize it to pass in the sequence name. – Electrodeposit 6/2, 2013 at 4:49

There is another way to reset a sequence in Oracle: set the maxvalue and cycle properties. When the nextval of the sequence hits the maxvalue, if the cycle property is set then it will begin again from the minvalue of the sequence.

The advantage of this method compared to setting a negative increment by is the sequence can continue to be used while the reset process runs, reducing the chance you need to take some form of outage to do the reset.

The value for maxvalue has to be greater than the current nextval, so the procedure below includes an optional parameter allowing a buffer in case the sequence is accessed again between selecting the nextval in the procedure and setting the cycle property.

create sequence s start with 1 increment by 1;

select s.nextval from dual
connect by level <= 20;

   NEXTVAL
----------
         1 
...
        20

create or replace procedure reset_sequence ( i_buffer in pls_integer default 0)
as
  maxval pls_integer;
begin

  maxval := s.nextval + greatest(i_buffer, 0); --ensure we don't go backwards!
  execute immediate 'alter sequence s cycle minvalue 0 maxvalue ' || maxval;
  maxval := s.nextval;
  execute immediate 'alter sequence s nocycle maxvalue 99999999999999';

end;
/
show errors

exec reset_sequence;

select s.nextval from dual;

   NEXTVAL
----------
         1

The procedure as stands still allows the possibility that another session will fetch the value 0, which may or may not be an issue for you. If it is, you could always:

Set minvalue 1 in the first alter
Exclude the second nextval fetch
Move the statement to set the nocycle property into another procedure, to be run at a later date (assuming you want to do this).

Rockett answered 10/2, 2013 at 17:9 Comment(0)

1) Suppose you create a SEQUENCE like shown below:

CREATE SEQUENCE TESTSEQ
INCREMENT BY 1
MINVALUE 1
MAXVALUE 500
NOCACHE
NOCYCLE
NOORDER

2) Now you fetch values from SEQUENCE. Lets say I have fetched four times as shown below.

SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual

3) After executing above four commands the value of the SEQUENCE will be 4. Now suppose I have reset the value of the SEQUENCE to 1 again. The follow the following steps. Follow all the steps in the same order as shown below:

ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
SELECT TESTSEQ.NEXTVAL FROM dual
ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
SELECT TESTSEQ.NEXTVAL FROM dual

Ramsay answered 11/12, 2009 at 4:6 Comment(0)

You can use the CYCLE option, shown below:

CREATE SEQUENCE test_seq
MINVALUE 0
MAXVALUE 100
START WITH 0
INCREMENT BY 1
CYCLE;

In this case, when the sequence reaches MAXVALUE (100), it will recycle to the MINVALUE (0).

In the case of a decremented sequence, the sequence would recycle to the MAXVALUE.

Lockup answered 25/3, 2014 at 15:51 Comment(1)

For the downvoters (who will never see this comment): The CYCLE attribute is exactly what I used to accomplish a sequence reset. The fact that the reset is automatic doesn't mean that it doesn't accomplish the goal--OP didn't specify that the reset had to be for a pre-existing sequence! – Chaffin 3/7, 2018 at 22:17

Jezus, all this programming for just an index restart... Perhaps I'm an idiot, but for pre-oracle 12 (which has a restart feature), what is wrong with a simpel:

drop sequence blah;
create sequence blah

Clothes answered 25/4, 2017 at 15:8 Comment(2)

The main problem with dropping a sequence is that it loses privileges granted on it. – Lauder 26/4, 2017 at 13:27

Ok granted, Jon. Mostly also restoring these will cost much less time than all that programming. Good DBA's usually have scripts so that shouldn't be an issue :-) – Clothes 23/6, 2017 at 14:55

Altering the sequence's INCREMENT value, incrementing it, and then altering it back is pretty painless, plus you have the added benefit of not having to re-establish all of the grants as you would had you dropped/recreated the sequence.

Breechloader answered 15/9, 2008 at 15:17 Comment(0)

I create a block to reset all my sequences:

DECLARE
    I_val number;
BEGIN
    FOR US IN
        (SELECT US.SEQUENCE_NAME FROM USER_SEQUENCES US)
    LOOP
        execute immediate 'select ' || US.SEQUENCE_NAME || '.nextval from dual' INTO l_val;
        execute immediate 'alter sequence ' || US.SEQUENCE_NAME || ' increment by -' || l_val || ' minvalue 0';
        execute immediate 'select ' || US.SEQUENCE_NAME || '.nextval from dual' INTO l_val;
        execute immediate 'alter sequence ' || US.SEQUENCE_NAME || ' increment by 1 minvalue 0';
    END LOOP;
END;

Dunnock answered 4/6, 2015 at 19:8 Comment(0)

Here's a more robust procedure for altering the next value returned by a sequence, plus a whole lot more.

First off it protects against SQL injection attacks since none of the strings passed in are used to directly create any of the dynamic SQL statements,
Second it prevents the next sequence value from being set outside the bounds of the min or max sequence values. The next_value will be != min_value and between min_value and max_value.
Third it takes the current (or proposed) increment_by setting as well as all the other sequence settings into account when cleaning up.
Fourth all parameters except the first are optional and unless specified take on the current sequence setting as defaults. If no optional parameters are specified no action is taken.
Finally if you try altering a sequence that doesn't exist (or is not owned by the current user) it will raise an ORA-01403: no data found error.

Here's the code:

CREATE OR REPLACE PROCEDURE alter_sequence(
    seq_name      user_sequences.sequence_name%TYPE
  , next_value    user_sequences.last_number%TYPE := null
  , increment_by  user_sequences.increment_by%TYPE := null
  , min_value     user_sequences.min_value%TYPE := null
  , max_value     user_sequences.max_value%TYPE := null
  , cycle_flag    user_sequences.cycle_flag%TYPE := null
  , cache_size    user_sequences.cache_size%TYPE := null
  , order_flag    user_sequences.order_flag%TYPE := null)
  AUTHID CURRENT_USER
AS
  l_seq user_sequences%rowtype;
  l_old_cache user_sequences.cache_size%TYPE;
  l_next user_sequences.min_value%TYPE;
BEGIN
  -- Get current sequence settings as defaults
  SELECT * INTO l_seq FROM user_sequences WHERE sequence_name = seq_name;

  -- Update target settings
  l_old_cache := l_seq.cache_size;
  l_seq.increment_by := nvl(increment_by, l_seq.increment_by);
  l_seq.min_value    := nvl(min_value, l_seq.min_value);
  l_seq.max_value    := nvl(max_value, l_seq.max_value);
  l_seq.cycle_flag   := nvl(cycle_flag, l_seq.cycle_flag);
  l_seq.cache_size   := nvl(cache_size, l_seq.cache_size);
  l_seq.order_flag   := nvl(order_flag, l_seq.order_flag);

  IF next_value is NOT NULL THEN
    -- Determine next value without exceeding limits
    l_next := LEAST(GREATEST(next_value, l_seq.min_value+1),l_seq.max_value);

    -- Grab the actual latest seq number
    EXECUTE IMMEDIATE
        'ALTER SEQUENCE '||l_seq.sequence_name
            || ' INCREMENT BY 1'
            || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number-l_old_cache)
            || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number)
            || ' NOCACHE'
            || ' ORDER';
    EXECUTE IMMEDIATE 
      'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL'
    INTO l_seq.last_number;

    l_next := l_next-l_seq.last_number-1;

    -- Reset the sequence number
    IF l_next <> 0 THEN
      EXECUTE IMMEDIATE 
        'ALTER SEQUENCE '||l_seq.sequence_name
            || ' INCREMENT BY '||l_next
            || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number)
            || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number)
            || ' NOCACHE'
            || ' ORDER';
      EXECUTE IMMEDIATE 
        'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL'
      INTO l_next;
    END IF;
  END IF;

  -- Prepare Sequence for next use.
  IF COALESCE( cycle_flag
             , next_value
             , increment_by
             , min_value
             , max_value
             , cache_size
             , order_flag) IS NOT NULL
  THEN
    EXECUTE IMMEDIATE 
      'ALTER SEQUENCE '||l_seq.sequence_name
          || ' INCREMENT BY '||l_seq.increment_by
          || ' MINVALUE '||l_seq.min_value
          || ' MAXVALUE '||l_seq.max_value
          || CASE l_seq.cycle_flag
             WHEN 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END
          || CASE l_seq.cache_size
             WHEN 0 THEN ' NOCACHE'
             ELSE ' CACHE '||l_seq.cache_size END
          || CASE l_seq.order_flag
             WHEN 'Y' THEN ' ORDER' ELSE ' NOORDER' END;
  END IF;
END;

Ohmmeter answered 14/8, 2015 at 19:32 Comment(0)

In my project, once it happened that someone manually entered the records without using sequence, hence I have to reset sequence value manually, for which I wrote below sql code snippet:

declare
max_db_value number(10,0);
cur_seq_value number(10,0);
counter number(10,0);
difference number(10,0);
dummy_number number(10);

begin

-- enter table name here
select max(id) into max_db_value from persons;
-- enter sequence name here
select last_number into cur_seq_value from user_sequences where  sequence_name = 'SEQ_PERSONS';

difference  := max_db_value - cur_seq_value;

 for counter in 1..difference
 loop
    -- change sequence name here as well
    select SEQ_PERSONS.nextval into dummy_number from dual;
 end loop;
end;

Please note, the above code will work if the sequence is lagging.

Gospodin answered 29/9, 2015 at 5:25 Comment(0)

I make an alternative that the user don’t need to know the values, the system get and use variables to update.

--Atualizando sequence da tabela SIGA_TRANSACAO, pois está desatualizada
DECLARE
 actual_sequence_number INTEGER;
 max_number_from_table INTEGER;
 difference INTEGER;
BEGIN
 SELECT [nome_da_sequence].nextval INTO actual_sequence_number FROM DUAL;
 SELECT MAX([nome_da_coluna]) INTO max_number_from_table FROM [nome_da_tabela];
 SELECT (max_number_from_table-actual_sequence_number) INTO difference FROM DUAL;
IF difference > 0 then
 EXECUTE IMMEDIATE CONCAT('alter sequence [nome_da_sequence] increment by ', difference);
 --aqui ele puxa o próximo valor usando o incremento necessário
 SELECT [nome_da_sequence].nextval INTO actual_sequence_number from dual;
--aqui volta o incremento para 1, para que futuras inserções funcionem normalmente
 EXECUTE IMMEDIATE 'ALTER SEQUENCE [nome_da_sequence] INCREMENT by 1';
 DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] foi atualizada.');
ELSE
 DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] NÃO foi atualizada, já estava OK!');
END IF;
END;

Coltish answered 28/4, 2016 at 20:5 Comment(0)

Here's how to make all auto-increment sequences match actual data:

Create a procedure to enforce next value as was already described in this thread:

CREATE OR REPLACE PROCEDURE Reset_Sequence(
    P_Seq_Name IN VARCHAR2,
    P_Val      IN NUMBER DEFAULT 0)
IS
  L_Current    NUMBER                      := 0;
  L_Difference NUMBER                      := 0;
  L_Minvalue User_Sequences.Min_Value%Type := 0;
BEGIN
  SELECT Min_Value
  INTO L_Minvalue
  FROM User_Sequences
  WHERE Sequence_Name = P_Seq_Name;
  EXECUTE Immediate 'select ' || P_Seq_Name || '.nextval from dual' INTO L_Current;
  IF P_Val        < L_Minvalue THEN
    L_Difference := L_Minvalue - L_Current;
  ELSE
    L_Difference := P_Val - L_Current;
  END IF;
  IF L_Difference = 0 THEN
    RETURN;
  END IF;
  EXECUTE Immediate 'alter sequence ' || P_Seq_Name || ' increment by ' || L_Difference || ' minvalue ' || L_Minvalue;
  EXECUTE Immediate 'select ' || P_Seq_Name || '.nextval from dual' INTO L_Difference;
  EXECUTE Immediate 'alter sequence ' || P_Seq_Name || ' increment by 1 minvalue ' || L_Minvalue;
END Reset_Sequence;

Create another procedure to reconcile all sequences with actual content:

CREATE OR REPLACE PROCEDURE RESET_USER_SEQUENCES_TO_DATA
IS
  STMT CLOB;
BEGIN
  SELECT 'select ''BEGIN'' || chr(10) || x || chr(10) || ''END;'' FROM (select listagg(x, chr(10)) within group (order by null) x FROM ('
    || X
    || '))'
  INTO STMT
  FROM
    (SELECT LISTAGG(X, ' union ') WITHIN GROUP (
    ORDER BY NULL) X
    FROM
      (SELECT CHR(10)
        || 'select ''Reset_Sequence('''''
        || SEQ_NAME
        || ''''','' || coalesce(max('
        || COL_NAME
        || '), 0) || '');'' x from '
        || TABLE_NAME X
      FROM
        (SELECT TABLE_NAME,
          REGEXP_SUBSTR(WTEXT, 'NEW\.(\S*) IS NULL',1,1,'i',1) COL_NAME,
          REGEXP_SUBSTR(BTEXT, '(\.|\s)([a-z_]*)\.nextval',1,1,'i',2) SEQ_NAME
        FROM USER_TRIGGERS
        LEFT JOIN
          (SELECT NAME BNAME,
            TEXT BTEXT
          FROM USER_SOURCE
          WHERE TYPE = 'TRIGGER'
          AND UPPER(TEXT) LIKE '%NEXTVAL%'
          )
        ON BNAME = TRIGGER_NAME
        LEFT JOIN
          (SELECT NAME WNAME,
            TEXT WTEXT
          FROM USER_SOURCE
          WHERE TYPE = 'TRIGGER'
          AND UPPER(TEXT) LIKE '%IS NULL%'
          )
        ON WNAME             = TRIGGER_NAME
        WHERE TRIGGER_TYPE   = 'BEFORE EACH ROW'
        AND TRIGGERING_EVENT = 'INSERT'
        )
      )
    ) ;
  EXECUTE IMMEDIATE STMT INTO STMT;
  --dbms_output.put_line(stmt);
  EXECUTE IMMEDIATE STMT;
END RESET_USER_SEQUENCES_TO_DATA;

NOTES:

Procedure extracts names from trigger code and does not depend on naming conventions
To check generated code before execution, switch comments on last two lines

Typhoeus answered 8/1, 2017 at 15:35 Comment(0)

-1

Stored procedure that worked for me

create or replace
procedure reset_sequence( p_seq_name in varchar2, tablename in varchar2 )
is
    l_val number;
    maxvalueid number;
begin
    execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
    execute immediate 'select max(id) from ' || tablename INTO maxvalueid;
    execute immediate 'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0';
    execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
    execute immediate 'alter sequence ' || p_seq_name || ' increment by '|| maxvalueid ||' minvalue 0';  
    execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
    execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;

How to use the stored procedure:

execute reset_sequence('company_sequence','company');

Hostility answered 18/11, 2019 at 22:3 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags