如何在Oracle 10g中每年自动将序列的值重置为0？

Question

就像在问题中一样，如何在Oracle 10g中每年自动将Oracle序列的值重置为0？

我正在使用序列生成格式的标识符，YYYY<sequence value>并且每年必须将序列值重置为0。

YYYY是从Java获得的，并与Oracle的序列值连接在一起。由于外部第三方的要求，无法更改标识符的格式。感谢您的任何帮助。

Answer 1

序列并不是真正要重置的设计。但是在某些情况下，需要重置序列，例如，在设置测试数据或将生产数据合并回测试环境中时。在生产中通常 不会 进行这种活动。

如果要将该类型的操作投入生产，则需要进行彻底的测试。（最令人担忧的是重置程序有可能在错误的时间（例如，年中）意外执行。

删除并重新创建序列是一种方法。作为一项操作，就SEQUENCE而言，它相当简单：

    DROP SEQUENCE MY_SEQ;
    CREATE SEQUENCE MY_SEQ以1递增1最小值0开始；

[编辑]正如Matthew Watson正确指出的那样，每个DDL语句（例如DROP，CREATE，ALTER）都将导致隐式提交。[/编辑]

但是，在SEQUENCE上授予的任何特权都将被删除，因此将需要重新授予这些特权。任何引用该序列的对象都将失效。为了使这个更通用，您需要保存特权（在删除序列之前），然后重新授予它们。

第二种方法是更改​​现有的SEQUENCE，而无需删除并重新创建它。可以通过将INCREMENT值更改为负值（当前值与0之间的差），然后精确地html" target="_blank">执行一个.NEXTVAL来将当前值设置为0，然后将INCREMENT更改回1，来完成重置序列的操作。之前（通常在测试环境中），我使用了相同的方法来将序列设置为更大的值。

当然，要使其正常工作，您需要 确保
在执行此操作时没有其他会话引用该序列。在错误的时间加上一个额外的.NEXTVAL将使复位加倍。（注意：如果应用程序以序列的所有者而不是单独的用户身份进行连接，则在数据库端实现该目标将很困难。）

为了使它每年发生，您需要安排一份工作。序列重置必须与标识符的YYYY部分的重置相协调。

[编辑]

未测试的 占位符，用于重置顺序的PL / SQL块的一种可能设计

    declare
      pragma autonomous_transaction;
      ln_increment       number;
      ln_curr_val        number;
      ln_reset_increment number;
      ln_reset_val       number;
    begin

      -- save the current INCREMENT value for the sequence
      select increment_by
        into ln_increment
        from user_sequences
       where sequence_name = 'MY_SEQ';

      -- determine the increment value required to reset the sequence
      -- from the next fetched value to 0
      select -1 - MY_SEQ.nextval into ln_reset_increment from dual;

      -- fetch the next value (to make it the current value)
      select MY_SEQ.nextval into ln_curr from dual;

      -- change the increment value of the sequence to 
      EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
        || ln_reset_increment ||' minvalue 0';

      -- advance the sequence to set it to 0
      select MY_SEQ.nextval into ln_reset_val from dual;

      -- set increment back to the previous(ly saved) value
      EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
        || ln_increment ;
    end;
    /

笔记：

• 重命名序列时，如何最好地保护序列以防访问？
• 这里有几个测试用例。
• 第一遍，检查正数，递增，递增1序列的规范情况。
• 更好的方法是创建新的SEQUENCE，添加权限，重命名现有序列和新序列，然后重新编译依赖项吗？