<p>In <a href="http://en.wikipedia.org/wiki/PostgreSQL" rel="noreferrer">PostgreSQL</a>, I can do something like this:</p>

<pre><code>ALTER SEQUENCE serial RESTART WITH 0;
</code></pre>

<p>Is there an Oracle equivalent?</p>

[{'answer_id': 51482, 'author': 'Mo.', 'author_id': 1870, 'author_profile': 'https://Stackoverflow.com/users/1870', 'pm_score': 7, 'selected': False, 'text': '<p>A true restart is not possible <a href="http://en.wiktionary.org/wiki/AFAIK" rel="noreferrer">AFAIK</a>. (Please correct me if I\'m wrong!).</p>\n\n<p>However, if you want to set it to 0, you can just delete and recreate it.</p>\n\n<p>If you want to set it to a specific value, you can set the INCREMENT to a negative value and get the next value.</p>\n\n<p>That is, if your sequence is at 500, you can set it to 100 via</p>\n\n<pre><code>ALTER SEQUENCE serial INCREMENT BY -400;\nSELECT serial.NEXTVAL FROM dual;\nALTER SEQUENCE serial INCREMENT BY 1;\n</code></pre>\n'}, {'answer_id': 63855, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 93633, 'author': 'Doug Porter', 'author_id': 4311, 'author_profile': 'https://Stackoverflow.com/users/4311', 'pm_score': 8, 'selected': True, 'text': '<p>Here is a good procedure for resetting any sequence to 0 from Oracle guru <a href="http://asktom.oracle.com" rel="noreferrer">Tom Kyte</a>.  Great discussion on the pros and cons in the links below too.</p>\n\n<pre><code>tkyte@TKYTE901.US.ORACLE.COM&gt; \ncreate or replace\nprocedure reset_seq( p_seq_name in varchar2 )\nis\n    l_val number;\nbegin\n    execute immediate\n    \'select \' || p_seq_name || \'.nextval from dual\' INTO l_val;\n\n    execute immediate\n    \'alter sequence \' || p_seq_name || \' increment by -\' || l_val || \n                                                          \' minvalue 0\';\n\n    execute immediate\n    \'select \' || p_seq_name || \'.nextval from dual\' INTO l_val;\n\n    execute immediate\n    \'alter sequence \' || p_seq_name || \' increment by 1 minvalue 0\';\nend;\n/\n</code></pre>\n\n<p>From this page: <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:951269671592" rel="noreferrer">Dynamic SQL to reset sequence value</a><br>\nAnother good discussion is also here: <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1119633817597" rel="noreferrer">How to reset sequences?</a></p>\n'}, {'answer_id': 1201816, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<p>This <a href="http://en.wikipedia.org/wiki/Stored_procedure" rel="noreferrer">stored procedure</a> restarts my sequence:</p>\n\n<pre><code>Create or Replace Procedure Reset_Sequence  \n  is\n  SeqNbr Number;\nbegin\n   /*  Reset Sequence \'seqXRef_RowID\' to 0    */\n   Execute Immediate \'Select seqXRef.nextval from dual \' Into SeqNbr;\n   Execute Immediate \'Alter sequence  seqXRef increment by - \' || TO_CHAR(SeqNbr) ;\n   Execute Immediate \'Select seqXRef.nextval from dual \' Into SeqNbr;\n   Execute Immediate \'Alter sequence  seqXRef increment by 1\';\nEND;\n</code></pre>\n\n<p>/</p>\n'}, {'answer_id': 1885714, 'author': 'Vipin Vij', 'author_id': 229348, 'author_profile': 'https://Stackoverflow.com/users/229348', 'pm_score': 2, 'selected': False, 'text': '<p>1) Suppose you create a SEQUENCE like shown below:</p>\n\n<pre><code>CREATE SEQUENCE TESTSEQ\nINCREMENT BY 1\nMINVALUE 1\nMAXVALUE 500\nNOCACHE\nNOCYCLE\nNOORDER\n</code></pre>\n\n<p>2) Now you fetch values from SEQUENCE. Lets say I have fetched four times as shown below.</p>\n\n<pre><code>SELECT TESTSEQ.NEXTVAL FROM dual\nSELECT TESTSEQ.NEXTVAL FROM dual\nSELECT TESTSEQ.NEXTVAL FROM dual\nSELECT TESTSEQ.NEXTVAL FROM dual\n</code></pre>\n\n<p>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:</p>\n\n<ol>\n<li><code>ALTER SEQUENCE TESTSEQ INCREMENT BY -3;</code></li>\n<li><code>SELECT TESTSEQ.NEXTVAL FROM dual</code></li>\n<li><code>ALTER SEQUENCE TESTSEQ INCREMENT BY 1;</code></li>\n<li><code>SELECT TESTSEQ.NEXTVAL FROM dual</code></li>\n</ol>\n'}, {'answer_id': 2866665, 'author': 'Gina', 'author_id': 345203, 'author_profile': 'https://Stackoverflow.com/users/345203', 'pm_score': 6, 'selected': False, 'text': '<p>This is my approach:</p>\n\n<ol>\n<li>drop the sequence</li>\n<li>recreate it</li>\n</ol>\n\n<p>Example:</p>\n\n<pre><code>--Drop sequence\n\nDROP SEQUENCE MY_SEQ;\n\n-- Create sequence \n\ncreate sequence MY_SEQ\nminvalue 1\nmaxvalue 999999999999999999999\nstart with 1\nincrement by 1\ncache 20;\n</code></pre>\n'}, {'answer_id': 4100161, 'author': 'Navin', 'author_id': 497595, 'author_profile': 'https://Stackoverflow.com/users/497595', 'pm_score': 3, 'selected': False, 'text': "<p>The following script set the sequence to a desired value:</p>\n\n<p>Given a freshly created sequence named PCS_PROJ_KEY_SEQ and table PCS_PROJ:</p>\n\n<pre><code>BEGIN\n   DECLARE\n      PROJ_KEY_MAX       NUMBER := 0;\n      PROJ_KEY_CURRVAL   NUMBER := 0;\n   BEGIN\n\n    SELECT MAX (PROJ_KEY) INTO PROJ_KEY_MAX FROM PCS_PROJ;\n    EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY ' || PROJ_KEY_MAX;\n    SELECT PCS_PROJ_KEY_SEQ.NEXTVAL INTO PROJ_KEY_CURRVAL FROM DUAL;\n    EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY 1';\n\nEND;\nEND;\n/\n</code></pre>\n"}, {'answer_id': 6062586, 'author': 'Allbite', 'author_id': 290091, 'author_profile': 'https://Stackoverflow.com/users/290091', 'pm_score': 5, 'selected': False, 'text': '<p>My approach is a teensy extension to <a href="https://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle/93633#93633">Dougman\'s example</a>.</p>\n\n<p>Extensions are...</p>\n\n<p>Pass in the seed value as a parameter.  Why?  I like to call the thing resetting the sequence back to <em>the max ID used in some table</em>.  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.</p>\n\n<p>It also honors the previous <em>minvalue</em>.  It may in fact <em>push the next value ever higher</em> if the desired <strong>p_val</strong> or <em>existing minvalue</em> are higher than the current or calculated next value.</p>\n\n<p>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.</p>\n\n<pre><code>create or replace\nprocedure Reset_Sequence( p_seq_name in varchar2, p_val in number default 0)\nis\n  l_current number := 0;\n  l_difference number := 0;\n  l_minvalue user_sequences.min_value%type := 0;\n\nbegin\n\n  select min_value\n  into l_minvalue\n  from user_sequences\n  where sequence_name = p_seq_name;\n\n  execute immediate\n  \'select \' || p_seq_name || \'.nextval from dual\' INTO l_current;\n\n  if p_Val &lt; l_minvalue then\n    l_difference := l_minvalue - l_current;\n  else\n    l_difference := p_Val - l_current;\n  end if;\n\n  if l_difference = 0 then\n    return;\n  end if;\n\n  execute immediate\n    \'alter sequence \' || p_seq_name || \' increment by \' || l_difference || \n       \' minvalue \' || l_minvalue;\n\n  execute immediate\n    \'select \' || p_seq_name || \'.nextval from dual\' INTO l_difference;\n\n  execute immediate\n    \'alter sequence \' || p_seq_name || \' increment by 1 minvalue \' || l_minvalue;\nend Reset_Sequence;\n</code></pre>\n\n<p>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...</p>\n\n<pre><code>create or replace\nprocedure Reset_Sequence_to_Data(\n  p_TableName varchar2,\n  p_FieldName varchar2\n)\nis\n  l_MaxUsed NUMBER;\nBEGIN\n\n  execute immediate\n    \'select coalesce(max(\' || p_FieldName || \'),0) from \'|| p_TableName into l_MaxUsed;\n\n  Reset_Sequence( p_TableName || \'_\' || p_Fieldname || \'_SEQ\', l_MaxUsed );\n\nEND Reset_Sequence_to_Data;\n</code></pre>\n\n<p>Now we\'re cooking with gas!  </p>\n\n<p>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 <em>"Reset_Sequence"</em> with that sensed max value.</p>\n\n<p>The final piece in this puzzle and the icing on the cake comes next...</p>\n\n<pre><code>create or replace\nprocedure Reset_All_Sequences\nis\nBEGIN\n\n  Reset_Sequence_to_Data( \'ACTIVITYLOG\', \'LOGID\' );\n  Reset_Sequence_to_Data( \'JOBSTATE\', \'JOBID\' );\n  Reset_Sequence_to_Data( \'BATCH\', \'BATCHID\' );\n\nEND Reset_All_Sequences;\n</code></pre>\n\n<p>In my actual database there are around one hundred other sequences being reset through this mechanism, so there are 97 more calls to <em>Reset_Sequence_to_Data</em> in that procedure above.</p>\n\n<p>Love it?  Hate it?  Indifferent?</p>\n'}, {'answer_id': 14800260, 'author': 'Chris Saxon', 'author_id': 1485955, 'author_profile': 'https://Stackoverflow.com/users/1485955', 'pm_score': 3, 'selected': False, 'text': "<p>There is another way to reset a sequence in Oracle: set the <code>maxvalue</code> and <code>cycle</code> properties. When the <code>nextval</code> of the sequence hits the <code>maxvalue</code>, if the <code>cycle</code> property is set then it will begin again from the <code>minvalue</code> of the sequence.</p>\n\n<p>The advantage of this method compared to setting a negative <code>increment by</code> 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.</p>\n\n<p>The value for <code>maxvalue</code> has to be greater than the current <code>nextval</code>, so the procedure below includes an optional parameter allowing a buffer in case the sequence is accessed again between selecting the <code>nextval</code> in the procedure and setting the <code>cycle</code> property.</p>\n\n<pre><code>create sequence s start with 1 increment by 1;\n\nselect s.nextval from dual\nconnect by level &lt;= 20;\n\n   NEXTVAL\n----------\n         1 \n...\n        20\n\ncreate or replace procedure reset_sequence ( i_buffer in pls_integer default 0)\nas\n  maxval pls_integer;\nbegin\n\n  maxval := s.nextval + greatest(i_buffer, 0); --ensure we don't go backwards!\n  execute immediate 'alter sequence s cycle minvalue 0 maxvalue ' || maxval;\n  maxval := s.nextval;\n  execute immediate 'alter sequence s nocycle maxvalue 99999999999999';\n\nend;\n/\nshow errors\n\nexec reset_sequence;\n\nselect s.nextval from dual;\n\n   NEXTVAL\n----------\n         1 \n</code></pre>\n\n<p>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:</p>\n\n<ul>\n<li>Set <code>minvalue 1</code> in the first alter</li>\n<li>Exclude the second <code>nextval</code> fetch </li>\n<li>Move the statement to set the <code>nocycle</code> property into another procedure, to be run at a later date (assuming you want to do this).</li>\n</ul>\n"}, {'answer_id': 19673327, 'author': 'Jon Heller', 'author_id': 409172, 'author_profile': 'https://Stackoverflow.com/users/409172', 'pm_score': 6, 'selected': False, 'text': '<p>For regular sequences:</p>\n<pre><code>alter sequence serial restart start with 1;\n</code></pre>\n<p>For system-generated sequences used for identity columns:</p>\n<pre><code>alter table table_name modify id generated by default on null as identity(start with 1);\n</code></pre>\n<hr />\n<p>This feature was officially added in 18c but is unofficially available since 12.1.</p>\n<p>It is arguably safe to use this undocumented feature in 12.1.  Even though the syntax is <em>not</em> included in the <a href="http://docs.oracle.com/database/121/SQLRF/statements_2014.htm#SQLRF00817" rel="nofollow noreferrer">official documentation</a>, it is generated by the Oracle package <a href="http://docs.oracle.com/database/121/ARPLS/d_metadiff.htm#ARPLS354" rel="nofollow noreferrer">DBMS_METADATA_DIFF</a>.  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.</p>\n<p>In 18c, the feature does not appear in the SQL Language Syntax, but is included in the <a href="https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-views-sequences-and-synonyms.html" rel="nofollow noreferrer">Database Administrator\'s Guide</a>.</p>\n'}, {'answer_id': 22639704, 'author': 'justincohler', 'author_id': 1133062, 'author_profile': 'https://Stackoverflow.com/users/1133062', 'pm_score': 2, 'selected': False, 'text': '<p>You can use the CYCLE option, shown below:</p>\n\n<pre><code>CREATE SEQUENCE test_seq\nMINVALUE 0\nMAXVALUE 100\nSTART WITH 0\nINCREMENT BY 1\nCYCLE;\n</code></pre>\n\n<p>In this case, when the sequence reaches MAXVALUE (100), it will recycle to the MINVALUE (0).</p>\n\n<p>In the case of a decremented sequence, the sequence would recycle to the MAXVALUE.</p>\n'}, {'answer_id': 30652117, 'author': 'Wendel', 'author_id': 2057463, 'author_profile': 'https://Stackoverflow.com/users/2057463', 'pm_score': 2, 'selected': False, 'text': "<p>I create a block to reset all my sequences:</p>\n\n<pre><code>DECLARE\n    I_val number;\nBEGIN\n    FOR US IN\n        (SELECT US.SEQUENCE_NAME FROM USER_SEQUENCES US)\n    LOOP\n        execute immediate 'select ' || US.SEQUENCE_NAME || '.nextval from dual' INTO l_val;\n        execute immediate 'alter sequence ' || US.SEQUENCE_NAME || ' increment by -' || l_val || ' minvalue 0';\n        execute immediate 'select ' || US.SEQUENCE_NAME || '.nextval from dual' INTO l_val;\n        execute immediate 'alter sequence ' || US.SEQUENCE_NAME || ' increment by 1 minvalue 0';\n    END LOOP;\nEND;\n</code></pre>\n"}, {'answer_id': 32017463, 'author': 'Sentinel', 'author_id': 4687355, 'author_profile': 'https://Stackoverflow.com/users/4687355', 'pm_score': 2, 'selected': False, 'text': "<p>Here's a more robust procedure for altering the next value returned by a sequence, plus a whole lot more.</p>\n\n<ul>\n<li>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,</li>\n<li>Second it prevents the next sequence value from being set outside the bounds of the min or max sequence values.  The <code>next_value</code> will be != <code>min_value</code> and between <code>min_value</code> and <code>max_value</code>.</li>\n<li>Third it takes the current (or proposed) <code>increment_by</code> setting as well as all the other sequence settings into account when cleaning up.</li>\n<li>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.</li>\n<li>Finally if you try altering a sequence that doesn't exist (or is not owned by the current user) it will raise an <code>ORA-01403: no data found</code> error.</li>\n</ul>\n\n<p>Here's the code:</p>\n\n<pre><code>CREATE OR REPLACE PROCEDURE alter_sequence(\n    seq_name      user_sequences.sequence_name%TYPE\n  , next_value    user_sequences.last_number%TYPE := null\n  , increment_by  user_sequences.increment_by%TYPE := null\n  , min_value     user_sequences.min_value%TYPE := null\n  , max_value     user_sequences.max_value%TYPE := null\n  , cycle_flag    user_sequences.cycle_flag%TYPE := null\n  , cache_size    user_sequences.cache_size%TYPE := null\n  , order_flag    user_sequences.order_flag%TYPE := null)\n  AUTHID CURRENT_USER\nAS\n  l_seq user_sequences%rowtype;\n  l_old_cache user_sequences.cache_size%TYPE;\n  l_next user_sequences.min_value%TYPE;\nBEGIN\n  -- Get current sequence settings as defaults\n  SELECT * INTO l_seq FROM user_sequences WHERE sequence_name = seq_name;\n\n  -- Update target settings\n  l_old_cache := l_seq.cache_size;\n  l_seq.increment_by := nvl(increment_by, l_seq.increment_by);\n  l_seq.min_value    := nvl(min_value, l_seq.min_value);\n  l_seq.max_value    := nvl(max_value, l_seq.max_value);\n  l_seq.cycle_flag   := nvl(cycle_flag, l_seq.cycle_flag);\n  l_seq.cache_size   := nvl(cache_size, l_seq.cache_size);\n  l_seq.order_flag   := nvl(order_flag, l_seq.order_flag);\n\n  IF next_value is NOT NULL THEN\n    -- Determine next value without exceeding limits\n    l_next := LEAST(GREATEST(next_value, l_seq.min_value+1),l_seq.max_value);\n\n    -- Grab the actual latest seq number\n    EXECUTE IMMEDIATE\n        'ALTER SEQUENCE '||l_seq.sequence_name\n            || ' INCREMENT BY 1'\n            || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number-l_old_cache)\n            || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number)\n            || ' NOCACHE'\n            || ' ORDER';\n    EXECUTE IMMEDIATE \n      'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL'\n    INTO l_seq.last_number;\n\n    l_next := l_next-l_seq.last_number-1;\n\n    -- Reset the sequence number\n    IF l_next &lt;&gt; 0 THEN\n      EXECUTE IMMEDIATE \n        'ALTER SEQUENCE '||l_seq.sequence_name\n            || ' INCREMENT BY '||l_next\n            || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number)\n            || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number)\n            || ' NOCACHE'\n            || ' ORDER';\n      EXECUTE IMMEDIATE \n        'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL'\n      INTO l_next;\n    END IF;\n  END IF;\n\n  -- Prepare Sequence for next use.\n  IF COALESCE( cycle_flag\n             , next_value\n             , increment_by\n             , min_value\n             , max_value\n             , cache_size\n             , order_flag) IS NOT NULL\n  THEN\n    EXECUTE IMMEDIATE \n      'ALTER SEQUENCE '||l_seq.sequence_name\n          || ' INCREMENT BY '||l_seq.increment_by\n          || ' MINVALUE '||l_seq.min_value\n          || ' MAXVALUE '||l_seq.max_value\n          || CASE l_seq.cycle_flag\n             WHEN 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END\n          || CASE l_seq.cache_size\n             WHEN 0 THEN ' NOCACHE'\n             ELSE ' CACHE '||l_seq.cache_size END\n          || CASE l_seq.order_flag\n             WHEN 'Y' THEN ' ORDER' ELSE ' NOORDER' END;\n  END IF;\nEND;\n</code></pre>\n"}, {'answer_id': 32836249, 'author': 'Rakesh', 'author_id': 1902897, 'author_profile': 'https://Stackoverflow.com/users/1902897', 'pm_score': 2, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>declare\nmax_db_value number(10,0);\ncur_seq_value number(10,0);\ncounter number(10,0);\ndifference number(10,0);\ndummy_number number(10);\n\nbegin\n\n-- enter table name here\nselect max(id) into max_db_value from persons;\n-- enter sequence name here\nselect last_number into cur_seq_value from user_sequences where  sequence_name = 'SEQ_PERSONS';\n\ndifference  := max_db_value - cur_seq_value;\n\n for counter in 1..difference\n loop\n    -- change sequence name here as well\n    select SEQ_PERSONS.nextval into dummy_number from dual;\n end loop;\nend;\n</code></pre>\n\n<p>Please note, the above code will work if the sequence is lagging. </p>\n"}, {'answer_id': 36924500, 'author': 'Bruno Freitas', 'author_id': 1322804, 'author_profile': 'https://Stackoverflow.com/users/1322804', 'pm_score': 1, 'selected': False, 'text': "<p>I make an alternative that the user don’t need to know the values, the system get and use variables to update.</p>\n\n<pre><code>--Atualizando sequence da tabela SIGA_TRANSACAO, pois está desatualizada\nDECLARE\n actual_sequence_number INTEGER;\n max_number_from_table INTEGER;\n difference INTEGER;\nBEGIN\n SELECT [nome_da_sequence].nextval INTO actual_sequence_number FROM DUAL;\n SELECT MAX([nome_da_coluna]) INTO max_number_from_table FROM [nome_da_tabela];\n SELECT (max_number_from_table-actual_sequence_number) INTO difference FROM DUAL;\nIF difference &gt; 0 then\n EXECUTE IMMEDIATE CONCAT('alter sequence [nome_da_sequence] increment by ', difference);\n --aqui ele puxa o próximo valor usando o incremento necessário\n SELECT [nome_da_sequence].nextval INTO actual_sequence_number from dual;\n--aqui volta o incremento para 1, para que futuras inserções funcionem normalmente\n EXECUTE IMMEDIATE 'ALTER SEQUENCE [nome_da_sequence] INCREMENT by 1';\n DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] foi atualizada.');\nELSE\n DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] NÃO foi atualizada, já estava OK!');\nEND IF;\nEND;\n</code></pre>\n"}, {'answer_id': 41534389, 'author': 'user46748', 'author_id': 5439593, 'author_profile': 'https://Stackoverflow.com/users/5439593', 'pm_score': 1, 'selected': False, 'text': "<p>Here's how to make all auto-increment sequences match actual data:</p>\n\n<ol>\n<li><p>Create a procedure to enforce next value as was already described in this thread:</p>\n\n<pre><code>CREATE OR REPLACE PROCEDURE Reset_Sequence(\n    P_Seq_Name IN VARCHAR2,\n    P_Val      IN NUMBER DEFAULT 0)\nIS\n  L_Current    NUMBER                      := 0;\n  L_Difference NUMBER                      := 0;\n  L_Minvalue User_Sequences.Min_Value%Type := 0;\nBEGIN\n  SELECT Min_Value\n  INTO L_Minvalue\n  FROM User_Sequences\n  WHERE Sequence_Name = P_Seq_Name;\n  EXECUTE Immediate 'select ' || P_Seq_Name || '.nextval from dual' INTO L_Current;\n  IF P_Val        &lt; L_Minvalue THEN\n    L_Difference := L_Minvalue - L_Current;\n  ELSE\n    L_Difference := P_Val - L_Current;\n  END IF;\n  IF L_Difference = 0 THEN\n    RETURN;\n  END IF;\n  EXECUTE Immediate 'alter sequence ' || P_Seq_Name || ' increment by ' || L_Difference || ' minvalue ' || L_Minvalue;\n  EXECUTE Immediate 'select ' || P_Seq_Name || '.nextval from dual' INTO L_Difference;\n  EXECUTE Immediate 'alter sequence ' || P_Seq_Name || ' increment by 1 minvalue ' || L_Minvalue;\nEND Reset_Sequence;\n</code></pre></li>\n<li><p>Create another procedure to reconcile all sequences with actual content:</p>\n\n<pre><code>CREATE OR REPLACE PROCEDURE RESET_USER_SEQUENCES_TO_DATA\nIS\n  STMT CLOB;\nBEGIN\n  SELECT 'select ''BEGIN'' || chr(10) || x || chr(10) || ''END;'' FROM (select listagg(x, chr(10)) within group (order by null) x FROM ('\n    || X\n    || '))'\n  INTO STMT\n  FROM\n    (SELECT LISTAGG(X, ' union ') WITHIN GROUP (\n    ORDER BY NULL) X\n    FROM\n      (SELECT CHR(10)\n        || 'select ''Reset_Sequence('''''\n        || SEQ_NAME\n        || ''''','' || coalesce(max('\n        || COL_NAME\n        || '), 0) || '');'' x from '\n        || TABLE_NAME X\n      FROM\n        (SELECT TABLE_NAME,\n          REGEXP_SUBSTR(WTEXT, 'NEW\\.(\\S*) IS NULL',1,1,'i',1) COL_NAME,\n          REGEXP_SUBSTR(BTEXT, '(\\.|\\s)([a-z_]*)\\.nextval',1,1,'i',2) SEQ_NAME\n        FROM USER_TRIGGERS\n        LEFT JOIN\n          (SELECT NAME BNAME,\n            TEXT BTEXT\n          FROM USER_SOURCE\n          WHERE TYPE = 'TRIGGER'\n          AND UPPER(TEXT) LIKE '%NEXTVAL%'\n          )\n        ON BNAME = TRIGGER_NAME\n        LEFT JOIN\n          (SELECT NAME WNAME,\n            TEXT WTEXT\n          FROM USER_SOURCE\n          WHERE TYPE = 'TRIGGER'\n          AND UPPER(TEXT) LIKE '%IS NULL%'\n          )\n        ON WNAME             = TRIGGER_NAME\n        WHERE TRIGGER_TYPE   = 'BEFORE EACH ROW'\n        AND TRIGGERING_EVENT = 'INSERT'\n        )\n      )\n    ) ;\n  EXECUTE IMMEDIATE STMT INTO STMT;\n  --dbms_output.put_line(stmt);\n  EXECUTE IMMEDIATE STMT;\nEND RESET_USER_SEQUENCES_TO_DATA;\n</code></pre></li>\n</ol>\n\n<p>NOTES:</p>\n\n<ol>\n<li>Procedure extracts names from trigger code and does not depend on naming conventions</li>\n<li>To check generated code before execution, switch comments on last two lines</li>\n</ol>\n"}, {'answer_id': 43614527, 'author': 'Lawrence', 'author_id': 1435079, 'author_profile': 'https://Stackoverflow.com/users/1435079', 'pm_score': 2, 'selected': False, 'text': "<p>Jezus, all this programming for just an index restart...\nPerhaps I'm an idiot, but for pre-oracle 12 (which has a restart feature), what is wrong with a simpel:</p>\n\n<pre><code>drop sequence blah;\ncreate sequence blah \n</code></pre>\n\n<p>?</p>\n"}, {'answer_id': 58923589, 'author': 'Jorge Santos Neill', 'author_id': 7994269, 'author_profile': 'https://Stackoverflow.com/users/7994269', 'pm_score': -1, 'selected': False, 'text': "<p>Stored procedure that worked for me</p>\n\n<pre><code>create or replace\nprocedure reset_sequence( p_seq_name in varchar2, tablename in varchar2 )\nis\n    l_val number;\n    maxvalueid number;\nbegin\n    execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;\n    execute immediate 'select max(id) from ' || tablename INTO maxvalueid;\n    execute immediate 'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0';\n    execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;\n    execute immediate 'alter sequence ' || p_seq_name || ' increment by '|| maxvalueid ||' minvalue 0';  \n    execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;\n    execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';\nend;\n</code></pre>\n\n<p>How to use the stored procedure:</p>\n\n<pre><code>execute reset_sequence('company_sequence','company');\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/51470', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/917/']