SQL 将 Oracle 序列重置为现有列中的下一个值的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6099108/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to reset an Oracle sequence to the next value in an existing column?
提问by Cade Roux
For some reason, people in the past have inserted data without using sequence.NEXTVAL. So when I go to use sequence.NEXTVAL in order to populate a table, I get a PK violation, since that number is already in use in the table.
出于某种原因，过去人们插入数据时没有使用sequence.NEXTVAL。因此，当我使用 sequence.NEXTVAL 来填充表时，我遇到了 PK 违规，因为该编号已在表中使用。
How can I update the next value so that it is usable? Right now, I'm just inserting over and over until it's successful (INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)), and that syncs up the nextval.
如何更新下一个值以使其可用？现在，我只是一遍又一遍地插入，直到它成功 ( INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL))，然后同步 nextval。
采纳答案by Cade Roux
These two procedures let me reset the sequence and reset the sequence based on data in a table (apologies for the coding conventions used by this client):
这两个过程让我重置序列并根据表中的数据重置序列（对此客户端使用的编码约定表示歉意）：
CREATE OR REPLACE PROCEDURE SET_SEQ_TO(p_name IN VARCHAR2, p_val IN NUMBER)
AS
l_num NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;
-- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer"
IF (p_val - l_num - 1) != 0
THEN
EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0';
END IF;
EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;
EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 ';
DBMS_OUTPUT.put_line('Sequence ' || p_name || ' is now at ' || p_val);
END;
CREATE OR REPLACE PROCEDURE SET_SEQ_TO_DATA(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
nextnum NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') + 1 AS n FROM ' || table_name INTO nextnum;
SET_SEQ_TO(seq_name, nextnum);
END;
回答by Basanth Roy
You can temporarily increase the cache size and do one dummy select and then reset the cache size back to 1. So for example
您可以临时增加缓存大小并执行一次虚拟选择，然后将缓存大小重置为 1。例如
ALTER SEQUENCE mysequence INCREMENT BY 100;
select mysequence.nextval from dual;
ALTER SEQUENCE mysequence INCREMENT BY 1;
回答by DCookie
If you can count on having a period of time where the table is in a stable state with no new inserts going on, this should do it (untested):
如果您可以指望有一段时间表处于稳定状态，没有新的插入，这应该可以做到（未经测试）：
DECLARE
last_used NUMBER;
curr_seq NUMBER;
BEGIN
SELECT MAX(pk_val) INTO last_used FROM your_table;
LOOP
SELECT your_seq.NEXTVAL INTO curr_seq FROM dual;
IF curr_seq >= last_used THEN EXIT;
END IF;
END LOOP;
END;
This enables you to get the sequence back in sync with the table, without dropping/recreating/re-granting the sequence. It also uses no DDL, so no implicit commits are performed. Of course, you're going to have to hunt down and slap the folks who insist on not using the sequence to populate the column...
这使您能够使序列与表同步，而无需删除/重新创建/重新授予序列。它还不使用 DDL，因此不执行隐式提交。当然，你将不得不追捕并扇那些坚持不使用序列来填充列的人......
回答by Leniel Maccaferri
In my case I have a sequence called PS_LOG_SEQwhich had a LAST_NUMBER = 3920.
在我来说，我有一个叫做序列PS_LOG_SEQ其中有一个LAST_NUMBER = 3920。
I then imported some data from PRODto my local machine and inserted into the PS_LOGtable. Production data had more than 20000rows with the latest LOG_ID (primary key) being 20070. After importing I tried to insert new rows in this table but when saving I got an exception like this one:
然后我将一些数据从PROD我的本地机器导入并插入到PS_LOG表中。生产数据有多个20000行，最新的 LOG_ID（主键）为 20070。导入后，我尝试在此表中插入新行，但保存时出现如下异常：
ORA-00001: unique constraint (LOG.PS_LOG_PK) violated
Surely this has to do with the Sequence PS_LOG_SEQassociated with the PS_LOGtable. The LAST_NUMBERwas colliding with data I imported which had already used the next ID value from the PS_LOG_SEQ.
当然，这PS_LOG_SEQ与与PS_LOG表关联的序列有关。的LAST_NUMBER是与I进口，其已经从使用的下一个ID值的数据碰撞PS_LOG_SEQ。
To solve that I used this command to update the sequence to the latest \ max(LOG_ID)+ 1:
为了解决这个问题，我使用此命令将序列更新为最新的 \ max(LOG_ID)+ 1：
alter sequence PS_LOG_SEQ restart start with 20071;
This command reset the LAST_NUMBERvalue and I could then insert new rows into the table. No more collision. :)
此命令重置该LAST_NUMBER值，然后我可以将新行插入表中。没有更多的碰撞。:)
Note:this alter sequencecommand is new in Oracle 12c.
注意：此alter sequence命令是 Oracle 12c 中的新命令。
回答by tech
With oracle 10.2g:
使用 Oracle 10.2g：
select level, sequence.NEXTVAL
from dual
connect by level <= (select max(pk) from tbl);
will set the current sequence value to the max(pk) of your table (i.e. the next call to NEXTVAL will give you the right result); if you use Toad, press F5 to run the statement, not F9, which pages the output (thus stopping the increment after, usually, 500 rows). Good side: this solution is only DML, not DDL. Only SQL and no PL-SQL. Bad side : this solution prints max(pk) rows of output, i.e. is usually slower than the ALTER SEQUENCE solution.
将当前序列值设置为表的 max(pk)（即下一次调用 NEXTVAL 将为您提供正确的结果）；如果您使用 Toad，请按 F5 运行语句，而不是 F9，它会分页输出（因此通常在 500 行后停止增量）。好的一面：这个解决方案只是 DML，而不是 DDL。只有 SQL 而没有 PL-SQL。不好的一面：此解决方案打印 max(pk) 行输出，即通常比 ALTER SEQUENCE 解决方案慢。
回答by dr fu manchu
Today, in Oracle 12c or newer, you probably have the column defined as GENERATED ... AS IDENTITY, and Oracle takes care of the sequence itself.
今天，在 Oracle 12c 或更新版本中，您可能将列定义为 GENERATED ... AS IDENTITY，并且 Oracle 自行处理序列。
You can use an ALTER TABLE Statement to modify "START WITH" of the identity.
您可以使用 ALTER TABLE 语句来修改身份的“START WITH”。
ALTER TABLE tbl MODIFY ("ID" NUMBER(13,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 3580 NOT NULL ENABLE);
