如何重置 Oracle 中的序列?
In PostgreSQL, I can do something like this:
ALTER SEQUENCE serial RESTART WITH 0;
Is there an Oracle equivalent?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
In PostgreSQL, I can do something like this:
ALTER SEQUENCE serial RESTART WITH 0;
Is there an Oracle equivalent?
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
接受
或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
发布评论
评论(18)
改变序列的 INCRMENT 值,增加它,然后将其改回来是非常轻松的,而且您还有一个额外的好处,即不必像删除/重新创建序列那样重新建立所有授权。
Altering the sequence's INCREMENT value, incrementing it, and then altering it back is pretty painless, plus you have the added benefit of not having to re-establish all of the grants as you would had you dropped/recreated the sequence.
在我的项目中,一旦发生有人手动输入记录而不使用序列,因此我必须手动重置序列值,为此我写了下面的sql代码片段:
请注意,如果序列滞后,上面的代码将起作用。
In my project, once it happened that someone manually entered the records without using sequence, hence I have to reset sequence value manually, for which I wrote below sql code snippet:
Please note, the above code will work if the sequence is lagging.
以下是如何使所有自动增量序列与实际数据匹配:
创建一个过程来强制执行下一个值,如本线程中已描述的那样:
创建另一个过程来协调所有序列与实际内容:
注意:
Here's how to make all auto-increment sequences match actual data:
Create a procedure to enforce next value as was already described in this thread:
Create another procedure to reconcile all sequences with actual content:
NOTES:
我做了一个替代方案,用户不需要知道这些值,系统获取并使用变量来更新。
I make an alternative that the user don’t need to know the values, the system get and use variables to update.
对我有用的存储过程
如何使用存储过程:
Stored procedure that worked for me
How to use the stored procedure:
这是一个更强大的过程,用于更改序列返回的下一个值,还有更多。
next_value
将是 !=min_value
并且介于min_value
和max_value
之间。increment_by
设置以及所有其他序列设置。ORA-01403:未找到数据
错误。这是代码:
Here's a more robust procedure for altering the next value returned by a sequence, plus a whole lot more.
next_value
will be !=min_value
and betweenmin_value
andmax_value
.increment_by
setting as well as all the other sequence settings into account when cleaning up.ORA-01403: no data found
error.Here's the code:
我创建一个块来重置所有序列:
I create a block to reset all my sequences:
您可以使用CYCLE选项,如下所示:
在这种情况下,当序列达到MAXVALUE(100)时,它将循环到MINVALUE(0)。
在递减序列的情况下,序列将循环到 MAXVALUE。
You can use the CYCLE option, shown below:
In this case, when the sequence reaches MAXVALUE (100), it will recycle to the MINVALUE (0).
In the case of a decremented sequence, the sequence would recycle to the MAXVALUE.
天哪,所有这些编程只是为了重新启动索引......
也许我是个白痴,但是对于 oracle 12 之前的版本(具有重新启动功能), simpel: 有什么问题吗
?
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:
?
1) 假设您创建一个如下所示的 SEQUENCE:
2) 现在您从 SEQUENCE 中获取值。 假设我已获取四次,如下所示。
3)执行完以上四个命令后,SEQUENCE 的值将为 4。现在假设我再次将 SEQUENCE 的值重置为 1。 请按照以下步骤操作。 按照如下所示的相同顺序执行所有步骤:
ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
从双中选择 TESTSEQ.NEXTVAL
ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
从双中选择 TESTSEQ.NEXTVAL
1) Suppose you create a SEQUENCE like shown below:
2) Now you fetch values from SEQUENCE. Lets say I have fetched four times as shown below.
3) After executing above four commands the value of the SEQUENCE will be 4. Now suppose I have reset the value of the SEQUENCE to 1 again. The follow the following steps. Follow all the steps in the same order as shown below:
ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
SELECT TESTSEQ.NEXTVAL FROM dual
ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
SELECT TESTSEQ.NEXTVAL FROM dual
在 Oracle 中还有另一种重置序列的方法:设置
maxvalue
和cycle
属性。 当序列的nextval
达到maxvalue
时,如果设置了cycle
属性,那么它将再次从minvalue
开始序列的代码>。与设置负增量相比,此方法的优点是在重置过程运行时可以继续使用序列,从而减少需要采取某种形式的中断来进行重置的机会。
maxvalue
的值必须大于当前nextval
,因此下面的过程包含一个可选参数,允许在选择之间再次访问序列时使用缓冲区>nextval
在过程中并设置cycle
属性。保持不变的过程允许另一个会话获取值 0,这对您来说可能是也可能不是问题。 如果是,您始终可以:
minvalue 1
nextval
fetchnocycle
属性设置为另一个程序,稍后运行(假设您想这样做)。There is another way to reset a sequence in Oracle: set the
maxvalue
andcycle
properties. When thenextval
of the sequence hits themaxvalue
, if thecycle
property is set then it will begin again from theminvalue
of the sequence.The advantage of this method compared to setting a negative
increment by
is 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.The value for
maxvalue
has to be greater than the currentnextval
, so the procedure below includes an optional parameter allowing a buffer in case the sequence is accessed again between selecting thenextval
in the procedure and setting thecycle
property.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:
minvalue 1
in the first alternextval
fetchnocycle
property into another procedure, to be run at a later date (assuming you want to do this).以下脚本将序列设置为所需值:
给定一个新创建的名为 PCS_PROJ_KEY_SEQ 的序列和表 PCS_PROJ:
The following script set the sequence to a desired value:
Given a freshly created sequence named PCS_PROJ_KEY_SEQ and table PCS_PROJ:
这个存储过程重新启动我的序列:
/
This stored procedure restarts my sequence:
/
我的方法是对 Dougman 示例的微小扩展。
扩展是...
传递种子值作为参数。 为什么? 我喜欢将序列重置回某个表中使用的最大ID。 我最终从另一个脚本调用这个过程,该脚本对一大堆序列执行多次调用,将 nextval 重置回某个级别,该级别足够高,不会导致主键违规,其中我使用序列的值作为唯一标识符。
它还遵循之前的最小值。 事实上,如果所需的p_val或现有最小值高于当前或计算出的下一个值,它可能将下一个值推得更高。
最重要的是,可以调用它来重置为指定值,然后等待,直到最后看到包装器“修复我的所有序列”过程。
该过程本身很有用,但现在让我们添加另一个过程,该过程调用它并使用序列命名约定以编程方式指定所有内容,并查找现有表/字段中使用的最大值...
现在我们正在用煤气做饭!
上面的过程将检查表中字段的最大值,从表/字段对构建序列名称,并使用检测到的最大值调用“Reset_Sequence”。
接下来是这个难题的最后一块,锦上添花......
在我的实际数据库中,大约有一百个其他序列通过此机制重置,因此还有 97 个对 Reset_Sequence_to_Data 的调用上面那个程序。
爱它? 讨厌它? 冷漠?
My approach is a teensy extension to Dougman's example.
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.
It also honors the previous minvalue. It may in fact push the next value ever higher if the desired p_val or existing minvalue are higher than the current or calculated next value.
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.
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...
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.
The final piece in this puzzle and the icing on the cake comes next...
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_Data in that procedure above.
Love it? Hate it? Indifferent?
这是我的方法:
示例:
This is my approach:
Example:
对于常规序列:
对于用于标识列的系统生成的序列:
此功能在 18c 中正式添加,但自 12.1 起非正式可用。
在 12.1 中使用这个未记录的功能可以说是安全的。 即使语法未包含在官方文档中文档,它是由Oracle包DBMS_METADATA_DIFF。 我已经在生产系统上使用过它几次。 但是,我创建了一个 Oracle 服务请求,他们验证了这不是文档错误,该功能确实不受支持。
在 18c 中,该功能未出现在 SQL 语言语法中,但包含在 数据库管理员指南。
For regular sequences:
For system-generated sequences used for identity columns:
This feature was officially added in 18c but is unofficially available since 12.1.
It is arguably safe to use this undocumented feature in 12.1. Even though the syntax is not included 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.
In 18c, the feature does not appear in the SQL Language Syntax, but is included in the Database Administrator's Guide.
真正的重启是不可能的AFAIK。 (如果我错了,请纠正我!)。
但是,如果您想将其设置为 0,则可以删除并重新创建它。
如果想将其设置为特定值,可以将INCRMENT设置为负值并获取下一个值。
也就是说,如果您的序列为 500,您可以通过以下方式将其设置为 100
A true restart is not possible AFAIK. (Please correct me if I'm wrong!).
However, if you want to set it to 0, you can just delete and recreate it.
If you want to set it to a specific value, you can set the INCREMENT to a negative value and get the next value.
That is, if your sequence is at 500, you can set it to 100 via
Oracle 大师 Tom Kyte 提供了一个将任何序列重置为 0 的好过程。 下面的链接也对利弊进行了精彩的讨论。
在此页面中: 要重置的动态 SQL序列值
另一个很好的讨论也在这里:如何重置序列?
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.
From this page: Dynamic SQL to reset sequence value
Another good discussion is also here: How to reset sequences?