清理Oracle序列 [英] Cleaning Up Oracle Sequences

问题描述

我已经与SQL Server进行了广泛的合作，但是对Oracle的经验却很少.我已经承担了清理" Oracle数据库中的序列的任务，并且不确定如何安全地进行此操作.

I've worked extensively with SQL Server but have very little experience with Oracle. I've been given the task of "cleaning up" the Sequences in an Oracle database and am not sure how to go about this safely.

我需要确定表中的最大值(例如ID = 105).然后查看该ID的下一个序列是什么.如果是106，那么一切都会很好.如果是110，那么我需要将其重置为106.

I need to determine the maximum value actually in the table (say the ID = 105). Then look and see what the next sequence for that ID is. If it is 106, then all would be good. If it were 110, then I need to reset it back to 106.

我可以安全地删除一个序列然后重新创建它吗，还是会破坏已经存在的主键?我猜这不会有问题，但是在我架起别人的系统之前，我想问一下.

Can I safely drop a sequence then recreate it or will that muck up the already existing Primary Key? I'm guessing this wouldn't be a problem but before I jacked up someone else's system, I wanted to ask.

这是我要使用的命令

Drop Sequence blah.foo_seq
Create Sequence blah.foo_seq Start WIth 106 Min 1 Max 2147483647 yada yada

推荐答案

我通常会警惕需要清理" Oracle序列.由于无法使用Oracle序列来生成无间隙值，因此，如果110会导致应用程序出现问题，则需要解决更大的问题.

I'd generally be wary about the need to "clean up" an Oracle sequence. Since Oracle sequences cannot be used to generate gap-free values, if 110 would cause a problem for the application, there are bigger problems that need to be addressed.

删除序列对主键没有影响.但是，它确实会使引用该序列的所有对象无效，并删除所有特权授予.重新创建序列后，您可以重新编译代码，因为特权的放弃可能会带来问题.

Dropping a sequence has no impact on a primary key. But it does invalidate any objects that reference the sequence and remove any privilege grants. You can recompile the code after you've re-created the sequence, it's the dropping of the privileges that would be potentially problematic.

一种避免处理丢失的特权的替代方法是更改​​INCREMENT BY参数以减小序列值，即

An alternative approach that avoids the need to deal with lost privileges would be to change the INCREMENT BY parameter to decrease the sequence value, i.e.

ALTER SEQUENCE foo_seq
  INCREMENT BY -4;

SELECT foo_seq.nextval
  FROM dual;

ALTER SEQUENCE foo_seq
  INCREMENT BY 1;

这篇关于清理Oracle序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！