Oracle序列以2而不是1开头 [英] Oracle Sequence starting with 2 instead of 1

问题描述

意外行为:

我遇到了11g的Oracle序列的奇怪行为(适用于10g):

I am encountering strange behavior of Oracle sequences with 11g (works with 10g):

CREATE SEQUENCE test_sequence START WITH 1;
CREATE TABLE test_table ( val INT );

INSERT INTO test_table VALUES ( test_sequence.NEXTVAL );

即使序列以 1 开头，插入的第一个值也是 2 :

Even though the sequence starts with 1, the first value inserted is 2:

SELECT * FROM test_table;

       VAL
----------
         2

---

预期的行为:

选择没有插入内容的NEXTVAL可以按预期工作:

Selecting NEXTVAL without the insert works as expected:

CREATE SEQUENCE test_sequence_2 START WITH 1;

SELECT test_sequence_2.NEXTVAL FROM dual

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

---

问题:

任何人都可以使用Oracle 11g重现吗?这是一个已知问题吗?

Can anyone reproduce this using Oracle 11g? Is this a known issue?

我正在使用
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.

I'm using
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.

推荐答案

这是记录在《 11.2 SQL语言参考》 中，

如果您尝试将序列值插入使用延迟段创建的表中，则该序列返回的第一个值将被跳过.

If you attempt to insert a sequence value into a table that uses deferred segment creation, the first value that the sequence returns will be skipped.

请参阅Jeffrey Kemp的答案中的链接，以获得My Oracle Support(Metalink)说明和解决方法.

See the link in Jeffrey Kemp's answer for a My Oracle Support (Metalink) note and a workaround.

这篇关于Oracle序列以2而不是1开头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！