如何在Oracle中重置序列？

在PostgreSQL中，我可以这样做：

ALTER SEQUENCE serial RESTART WITH 0;

是否有与Oracle相当的产品？

680535 次浏览
小开

阿法伊克，不可能真正重新启动。（如果我说错了，请纠正我！

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

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

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

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

更改序列的增量值，增加它，然后将其更改回来是非常轻松的，此外，您还可以获得额外的好处，即不必像删除/重新创建序列那样重新建立所有授权。

小开
最佳答案

下面是从Oracle Guru汤姆·凯特将任何序列重置为0的一个很好的过程。在下面的链接中也有关于利弊的很好的讨论。

tkyte@TKYTE901.US.ORACLE.COM>
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;
/
本页

起：要重置序列值的动态SQL
另一个很好的讨论也在这里：如何重置序列？

小开

此存储过程重新启动我的序列：

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;

/

小开

1）假设您创建了一个如下所示的序列：

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

2）现在从序列中获取值。假设我已经获取了四次，如下所示。

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

3）执行上述四个命令后，序列的值将为4。现在假设我已将序列的值重新设置为1。遵循以下步骤。按照如下所示的相同顺序执行所有步骤：

  1. ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
  2. SELECT TESTSEQ.NEXTVAL FROM dual
  3. ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
  4. SELECT TESTSEQ.NEXTVAL FROM dual
小开

这是我的方法：

  1. 放下序列
  2. 重新创建它

示例：

--Drop sequence


DROP SEQUENCE MY_SEQ;


-- Create sequence


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

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

给定新创建的名为PCs_PROJ_密钥_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;
/
小开

我的方法是对道格曼的例子的一个小小的扩展。

扩展名是..

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

它还符合先前的最小值。如果期望的P_Val或现有最小值高于当前值或计算的下一个值，则实际上可以将下一个值推得更高。

最棒的是，可以调用它来重置为指定的值，然后等待，直到最后看到包装器“修复我的所有序列”过程。

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 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;

现在我们用煤气做饭！

上面的过程将检查表中的字段的最大值，从表/字段对构建序列名称，并调用具有所感测的最大值的“重置_序列”。

接下来是这个拼图的最后一块，也是蛋糕上的糖衣。

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;

在我的实际数据库中，大约有100个其他序列通过此机制重置，因此在上述过程中还有97个对将_序列_复位到_数据的调用。

喜欢吗？讨厌它？冷漠？

小开

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

与设置负increment by相比，这种方法的优势在于，在复位过程运行时，该序列可以继续使用，从而减少了您需要采取某种形式的中断来执行复位的机会。

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

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

  • 在第一次变更中设置minvalue 1
  • 排除第二个nextval获取
  • 将用于设置nocycle属性的语句移到另一个过程中，以便以后运行（假设您希望这样做）。
小开

对于常规序列：

alter sequence serial restart start with 1;

对于用于标识列的系统生成的序列：

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

此功能是在18C中正式添加的，但从12.1开始非正式提供。

可以说，在12.1中使用此未记录的功能是安全的。即使语法是正式文件中包括的不，它也是由Oracle包DBMS_元数据_差异生成的。我在生产系统上使用过几次。但是，我创建了一个Oracle服务请求，他们验证了这不是一个文档错误，该功能确实不受支持。

在18C中，该特性不出现在SQL语言语法中，而是包含在数据库管理员指南中。

小开

您可以使用“循环”选项，如下所示：

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

在这种情况下，当序列达到最大值（100）时，它将循环到最小值（0）。

在递减序列的情况下，序列将循环到最大值。

小开

我创建了一个块来重置我的所有序列：

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;
小开

下面是一个更健壮的过程，用于更改序列返回的下一个值，以及更多内容。

  • 首先，它可以防止SQL注入攻击，因为传入的字符串都不会用于直接创建任何动态SQL语句。
  • 其次，它防止下一个序列值被设置在最小或最大序列值的边界之外。next_value将为！=min_value和max_value之间的min_value。
  • 第三，在清理时，它考虑当前（或建议的）increment_by设置以及所有其他序列设置。
  • 第四，除第一个参数外的所有参数都是可选的，除非指定，否则将当前序列设置作为默认值。如果未指定可选参数，则不执行任何操作。
  • 最后，如果你试图改变一个不存在的序列（或者不属于当前用户），它将引发一个ORA-01403: no data found错误。

代码如下：

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;
小开

在我的项目中，有一次有人在没有使用序列的情况下手动输入了记录，因此我必须手动重置序列值，为此我在下面写了SQL代码片段：

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;

请注意，如果序列滞后，上面的代码将起作用。

小开

我做了一个替代方案，用户不需要知道值，系统获取并使用变量来更新。

--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;
小开

下面介绍如何使所有自动递增序列与实际数据匹配：

  1. 创建一个过程以强制执行下一个值，如本主题中所述：

    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;

  2. 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:

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

Jezus，所有这些编程只是为了索引重启.. 也许我是个白痴，但对于Oracle 12之前的版本（具有重启功能），Simple有什么问题：

drop sequence blah;
create sequence blah

?

小开

为我工作的存储过程

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;

如何使用存储过程：

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

提交答案