如何更改数据库序列起始值 [英] How to change db sequence start value

问题描述

我的要求如下. (a)我已经创建了序列，并且有一个表(假设雇员具有id，name..etc等). (b)我的序列如何损坏，序列的当前值与雇员表的id列的最大值不同步.

My requirement is as follows. (a) I have already sequence created and one table is (lets assume employee having id,name..etc). (b) Some how my sequence get corrupted and currently current value of sequence is not sync with the max value of id column of employee table.

现在我想将我的序列重置为employee表的id列的最大值.我知道我们可以通过使用PL/SQL，存储过程轻松地做到这一点.但是我想编写简单的查询，它将执行以下任务. 1-获取id的最大值和我序列的当前值.取一个差值并通过使用by by将其添加到序列中.(这里我序列的当前值小于id列的最大id值)

now i want to reset my sequence to the max value of the id column of employee table. i know we can do it easily by using PL/SQL,Stored procedure. but i want to write plain query which will do following tasks. 1- Fetch max value of id and current value of my sequence .Take a difference and add that difference to the sequence by using increment by.( here my current value of sequence is lesser than max id value of id column)

推荐答案

由于alter sequence是DDL，因此无法更改普通SQL的增量，因此您需要一次一次地多次递增.这将使序列的数量增加到您当前拥有的最高ID的次数:

You can't change the increment from plain SQL as alter sequence is DDL, so you need to increment it multiple times, one by one. This would increment the sequence as many times as the highest ID you currently have:

select your_sequence.nextval
from (
  select max(id) as max_id
  from your_table
) t
connect by level < t.max_id;

SQL Fiddle演示(有点困惑，因为如果模式已被缓存).

SQL Fiddle demo (fudged a bit as the sequence isn't reset if the schema is cached).

如果您的价值很高，那可能效率不高，不过作为一次调整，这可能无关紧要.您不能在子查询或CTE中引用当前序列值，但可以查看USER_SEQUNECES视图以粗略了解开始的距离，并将调用次数减少到两倍以内大小(取决于缓存中包含多少个等待值):

If you have a high value though that might be inefficient, though as a one-off adjustment that probably doesn't matter. You can't refer to the current sequence value in a subquery or CTE, but you could look at the USER_SEQUNECES view to get a rough guide of how far out you are to begin with, and reduce the number of calls to within double the case size (depending on how many waiting values the cache holds):

select your_sequence.nextval
from (
  select max(id) as max_id
  from your_table
) t
connect by level <= (
  select t.max_id + us.cache_size + 1 - us.last_number
  from user_sequences us
  where sequence_name = 'YOUR_SEQUENCE'
);

SQL小提琴.

在现有ID值较低的情况下，第二个可能会做更多的工作，但是在ID值较高的情况下，您会看到第二个有自己的作用.

With low existing ID values the second one might do more work, but with higher values you can see the second comes into its own a bit.

这篇关于如何更改数据库序列起始值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！