SQL 如何在 Oracle 中重置序列？

How do I reset a sequence in Oracle?

提问by Mauli

In PostgreSQL, I can do something like this:

在PostgreSQL，我可以做这样的事情：

ALTER SEQUENCE serial RESTART WITH 0;

Is there an Oracle equivalent?

是否有 Oracle 等价物？

采纳答案by Doug Porter

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.

这是一个很好的过程，用于将 Oracle 大师Tom Kyte 的任何序列重置为 0 。在下面的链接中也对利弊进行了很好的讨论。

[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?

来自此页面：动态 SQL 重置序列值
另一个很好的讨论也在这里：如何重置序列？

回答by Mo.

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

AFAIK不可能真正重启。（如果我错了，请纠正我！）。

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

但是，如果要将其设置为 0，则只需删除并重新创建即可。

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

如果要将其设置为特定值，可以将 INCREMENT 设置为负值并获取下一个值。

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

也就是说，如果您的序列为 500，则可以通过以下方式将其设置为 100

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

回答by Gina

This is my approach:

这是我的方法：

1. drop the sequence
2. recreate it

1. 删除序列
2. 重新创建它

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;

回答by Jon Heller

alter sequence serial restart start with 1;

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

此功能在 18c 中正式添加，但在 12.1 中非正式可用。

It is arguably safe to use this undocumented feature in 12.1. Even though the syntax is notincluded 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.

在 12.1 中使用这个未记录的功能可以说是安全的。尽管官方文档中没有包含该语法，但它是由 Oracle 包DBMS_METADATA_DIFF生成的。我已经在生产系统上多次使用它。但是，我创建了一个 Oracle 服务请求，他们确认这不是文档错误，该功能确实不受支持。

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

在 18c 中，该功能没有出现在 SQL 语言语法中，但包含在数据库管理员指南中。

回答by Allbite

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

我的方法是对Dougman 示例的一个很小的扩展。

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.

将种子值作为参数传递。为什么？我喜欢将序列重置为某些表中使用的最大 ID。我最终从另一个脚本调用这个 proc，该脚本对一大堆序列执行多次调用，将 nextval 重置回某个级别，该级别足够高，不会导致主键冲突，其中我使用序列的值作为唯一标识符。

It also honors the previous minvalue. It may in fact push the next value ever higherif the desired p_valor existing minvalueare higher than the current or calculated next value.

它还尊重以前的minvalue。如果所需的p_val或现有最小值高于当前或计算出的下一个值，它实际上可能会将下一个值推得更高。

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.

上面的过程将检查表中字段的最大值，从表/字段对构建序列名称，并使用检测到的最大值调用“Reset_Sequence”。

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_Datain that procedure above.

在我的实际数据库中，大约有一百个其他序列通过这种机制被重置，因此在上面的过程中还有 97 个对Reset_Sequence_to_Data 的调用。

Love it? Hate it? Indifferent?

爱它？讨厌它？冷漠？

回答by Navin

The following script set the sequence to a desired value:

以下脚本将序列设置为所需值：

Given a freshly created sequence named PCS_PROJ_KEY_SEQ and table PCS_PROJ:

给定一个新创建的名为 PCS_PROJ_KEY_SEQ 的序列和表 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;
/

回答by Navin

This stored procedurerestarts 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;

/

/

回答by Chris Saxon

There is another way to reset a sequence in Oracle: set the maxvalueand cycleproperties. When the nextvalof the sequence hits the maxvalue, if the cycleproperty is set then it will begin again from the minvalueof the sequence.

在 Oracle 中还有另一种重置序列的方法：设置maxvalue和cycle属性。当nextval序列的 命中 时maxvalue，如果cycle设置了该属性，那么它将从minvalue序列的再次开始。

The advantage of this method compared to setting a negative increment byis 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.

与设置负数相比，此方法的优点increment by是可以在重置过程运行时继续使用序列，从而减少您需要采取某种形式的中断来进行重置的机会。

The value for maxvaluehas 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 nextvalin the procedure and setting the cycleproperty.

for 的值maxvalue必须大于当前nextval，因此下面的过程包含一个可选参数，允许缓冲区，以防在选择nextval过程中的 和设置cycle属性之间再次访问序列。

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:

该过程仍然允许另一个会话获取值 0 的可能性，这对您来说可能是也可能不是问题。如果是这样，您可以随时：

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

• 设置minvalue 1在第一个更改
• 排除第二次nextval提取
• 移动语句以将nocycle属性设置到另一个过程中，以便在以后运行（假设您要这样做）。

回答by Lawrence

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:

天哪，所有这些编程只是为了重新启动索引......也许我是个白痴，但是对于 pre-oracle 12（具有重新启动功能），simpel 有什么问题：

drop sequence blah;
create sequence blah 

?

?

回答by Wendel

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;