首页 / 知识

关于sql：如何在Oracle中重置序列？

2023-04-14 16:07:00

How do I reset a sequence in Oracle?

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

1	ALTER SEQUENCE serial RESTART WITH 0;

是否有Oracle等效产品？

这是一个从Oracle专家Tom Kyte将任何序列重置为0的好方法。在下面的链接中也对正反两方面进行了精彩的讨论。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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
这里也有另一个很好的讨论：如何重置序列？

不能真正重启AFAIK。 (如果我错了，请纠正我！)。

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

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

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

1
2
3

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

这是我的方法：

删除序列

重新创建

例：

1
2
3
4
5
6
7
8
9
10
11
12

--Drop sequence

DROP SEQUENCE MY_SEQ;

-- Create sequence

CREATE SEQUENCE MY_SEQ
minvalue 1
maxvalue 999999999999999999999
START WITH 1
INCREMENT BY 1
cache 20;

我的方法是对Dougman的例子进行小小的扩展。

扩展是...

传递种子值作为参数。为什么？我喜欢称之为将序列重置为某些表中使用的最大ID的东西。我最终从另一个脚本中调用了此proc，该脚本对整个序列序列执行了多次调用，将nextval重置回了足够高的水平，以至于不会在我将序列值用作唯一标识符的情况下引起主键冲突。

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

最棒的是，可以调用它以将其重置为指定的值，然后等到包装器最后"修复所有序列"过程后再调用它。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

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;

该过程本身就非常有用，但是现在让我们添加另一个过程，该过程调用它并使用序列命名约定以编程方式指定所有内容，并查找现有表/字段中使用的最大值...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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;

现在，我们正在用天然气烹饪！

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

接下来是这个难题的最后一块，还有锦上添花。

1
2
3
4
5
6
7
8
9
10

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;

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

爱它？讨厌它？冷漠？

1	ALTER SEQUENCE serial restart START WITH 1;

此功能已在18c中正式添加，但在12.1中才正式提供。

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

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

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

给定一个新创建的名为PCS_PROJ_KEY_SEQ的序列和表PCS_PROJ：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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

1
2
3
4
5
6
7
8
9
10

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;

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

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属性设置为另一个过程，该过程将在以后运行(假设您要执行此操作)。

Jezus，所有这些仅用于索引的编程都将重新启动...
也许我是个白痴，但是对于Oracle 12之前的版本(具有重新启动功能)，simpel有什么问题：

1 2	DROP SEQUENCE blah; CREATE SEQUENCE blah

？

这是一个更健壮的过程，用于更改序列返回的下一个值，以及更多功能。

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

这是代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

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;

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

1
2
3
4
5
6
7
8
9
10
11
12

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代码段中写道：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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;

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

1)假设您创建一个SEQUENCE，如下所示：

1
2
3
4
5
6
7

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

2)现在，您从SEQUENCE获取值。可以说我拿了四次，如下所示。

1
2
3
4

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

3)执行完上述四个命令后，SEQUENCE的值将为4。现在假设我已将SEQUENCE的值再次重置为1。请遵循以下步骤。按照如下所示的顺序执行所有步骤：

ALTER SEQUENCE TESTSEQ INCREMENT BY -3;

SELECT TESTSEQ.NEXTVAL FROM dual

ALTER SEQUENCE TESTSEQ INCREMENT BY 1;

SELECT TESTSEQ.NEXTVAL FROM dual

更改序列的INCREMENT值，将其递增，然后再将其更改回是很轻松的，此外，您还具有不必像删除/重新创建序列那样重新建立所有授予的额外好处。

您可以使用CYCLE选项，如下所示：

1
2
3
4
5
6

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

在这种情况下，当序列达到MAXVALUE(100)时，它将循环到MINVALUE(0)。

如果序列递减，则该序列将回收到MAXVALUE。

我的另一种选择是用户不需要知道值，系统便可以获取并使用变量进行更新。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

--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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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;

创建另一个过程以使所有序列与实际内容一致：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

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;

笔记：

过程从触发器代码中提取名称，并且不依赖于命名约定

要在执行之前检查生成的代码，请在最后两行切换注释

对我有用的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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;

如何使用存储过程：