如何更改动态 SQL 中的序列？

我正在尝试创建一个脚本来将数据从一个数据库迁移到另一个数据库。我当前无法做的一件事是将序列的 nextval 设置为另一个数据库中序列的 ​​nextval。

我从 user_sequences 中得到了值的差异，并生成了以下动态 SQL 语句：

execute immediate 'alter sequence myseq increment by 100';
execute immediate 'select myseq.nextval from dual';
execute immediate 'alter sequence myseq increment by 1';

commit;

但什么也没发生。我缺少什么？如果我在程序之外运行相同的语句，它们可以正常工作：

alter sequence myseq increment by 100;
select myseq.nextval from dual;
alter sequence myseq increment by 1;

commit;

EDIT: 抱歉大家没说清楚。我实际上正在改变同一个数据库中的序列。我只是从远程数据库获取要设置的值。也许没有必要提及远程数据库，因为它不会影响事物。我提到它只是为了解释我的目标是什么。

步骤 1. 我从远程数据库获取序列的 nextval。

select (select last_number
        from dba_sequences@remoteDB
        where upper(sequence_name) = upper(v_sequence_name)) - (select last_number
                                                                from user_sequences
                                                                where upper(sequence_name) = upper(v_sequence_name)) increment_by
from dual;    

步骤 2. 我生成具有以下值的动态 SQL 语句：

execute immediate 'alter sequence myseq increment by 100';
execute immediate 'select myseq.nextval from dual';
execute immediate 'alter sequence myseq increment by 1';

commit;

没有引发错误，但什么也没有发生。当我使用 DBMS_OUTPUT.PUT_LINE 编写 SQL 语句并在外部运行它们时，它们起作用了。

下面是一些将序列动态设置为新（更高）值的代码。我已经编写了此内容，因此它适用于您的模式中的任何序列。

create or replace procedure resync_seq
    (p_seq_name in user_sequences.sequence_name%type)
is
    local_val pls_integer;
    remote_val pls_integer;
    diff pls_integer;
begin
    execute immediate 'select '|| p_seq_name ||'.nextval from dual'
           into local_val;
    select last_number into remote_val
    from user_sequences@remote_db
    where sequence_name = p_seq_name ;
    diff := remote_val - local_val;

    if diff > 0
    then
        execute immediate 'alter sequence  '|| p_seq_name ||' increment by ' ||to_char(diff);
        execute immediate 'select '|| p_seq_name ||'.nextval from dual'
           into local_val;
        execute immediate 'alter sequence  '|| p_seq_name ||' increment by 1';
    end if;

end;

该过程不需要 COMMIT，因为 DDL 语句发出隐式提交（实际上是两次）。

您可以执行它并查看同步值，如下所示（在 SQL*PLus 中）：

exec resync_seq('MYSEQ')
select myseq.currval
from dual

顺便说一句，重置序列（为其原始起始值或不同的较低值）的唯一方法是删除并重新创建序列。

在 18c 中，Oracle 在 ALTER SEQUENCE 中添加了 RESTART 功能。最直接的选择...

alter sequence myseq restart;

...将序列重置为原始 CREATE SEQUENCE 语句中 START WITH 子句指定的值。另一个选项允许我们指定一个new初始点：

alter sequence myseq restart start with 23000;

令人兴奋的是，这个新的起点可以领先或落后于当前值（在序列的通常范围内）。

一个障碍是这一新功能没有记录（仅供 Oracle 内部使用），因此我们不应该使用它。在 20 世纪仍然如此。 https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/ALTER-SEQUENCE.html#GUID-A6468B63-E7C9-4EF0-B048-82FE2449B26D唯一批准的用于更改序列值的机制是我上面概述的机制。