oracle - 将序列的值从一个 Oracle 数据库复制到另一个

Question

是否可以将模式中序列的当前值复制到另一个数据库？序列已在两个数据库中创建。这是在甲骨文。

编辑:

根据下面的帮助，一旦建立了数据库链接，此脚本将确保目标数据库序列值大于或等于源数据库值。这样做的动机是为了让我们在复制数据后不会出现主键错误，因此目标数字不准确的事实是没有问题的。

set serveroutput on
DECLARE
  CURSOR GetCursorsToSync
  is
  SELECT a.sequence_name, a.last_number last_number_a, b.last_number last_number_b
  FROM user_sequences@SOURCE_DB a, user_sequences b
  where a.sequence_name = b.sequence_name
  and a.last_number != b.last_number;

  type CursorsTableType is table of GetCursorsToSync%rowtype index by pls_integer;  
  CursorsTable  CursorsTableType;
  i             pls_integer;

  PROCEDURE reset_sequence(
      sequence_name IN VARCHAR2,
      source_value  IN NUMBER,
      target_value  IN NUMBER )
  IS
    l_sql varchar2(4000);
    l_temp number(30);
  BEGIN
    IF source_value <= target_value THEN
      RETURN;
    END IF;

    dbms_output.put_line(sequence_name || ' ' || source_value || ' ' || target_value);
    l_sql := 'alter sequence '|| sequence_name || ' increment by '||to_char(source_value-target_value);
    dbms_output.put_line(l_sql);
    EXECUTE immediate l_sql;
    l_sql := 'SELECT '|| sequence_name || '.nextval FROM dual';
    dbms_output.put_line(l_sql);
    EXECUTE immediate l_sql into l_temp;
    dbms_output.put_line(l_temp);
    l_sql := 'alter sequence '|| sequence_name || ' increment by 1';
    dbms_output.put_line(l_sql);
    EXECUTE immediate l_sql;
    COMMIT;
  END reset_sequence;
BEGIN
  open GetCursorsToSync;
  fetch GetCursorsToSync bulk collect into CursorsTable;
  close GetCursorsToSync;
  commit;

  i := CursorsTable.first;
  while i is not null loop
    reset_sequence(CursorsTable(i).sequence_name,
      CursorsTable(i).last_number_a,CursorsTable(i).last_number_b);
    i := CursorsTable.next(i);
  end loop;
end;
/

Answer 1

UltraCommits 语句和数据库链接的组合，以及您可以安排自动运行的存储过程，将为您提供良好的服务。

--drop create db_link
DROP DATABASE LINK SOURCE_DB;

CREATE DATABASE LINK "SOURCE_DB"
  CONNECT TO USER IDENTIFIED BY password USING 'SOURCE_DB';

 --drop create sequences 
  DROP sequence target_seq;
CREATE sequence target_seq start with 6;

  --the next two lines run in source db
  DROP sequence source_seq;
CREATE sequence source_seq start with 6000;

--take a look at the sequences to get an idea of what to expect
SELECT source_schema.source_seq.nextval@SOURCE_DB source_seq,
  target_seq.nextval target_seq
FROM dual; 

--create procedure to reset target sequence that you can schedule to automatically run
CREATE OR REPLACE
PROCEDURE reset_sequence
AS
  l_source_sequence pls_integer;
  l_target_sequence pls_integer;
  l_sql VARCHAR2(100);
BEGIN
  SELECT source_schema.source_seq.nextval@SOURCE_DB,
    target_seq.nextval
  INTO l_source_sequence,
    l_target_sequence
  FROM dual;
  l_sql := 'alter sequence target_seq increment by '||to_number(l_source_sequence-l_target_sequence);
  EXECUTE immediate l_sql;
  SELECT target_seq.nextval INTO l_target_sequence FROM dual;
  l_sql := 'alter sequence target_seq increment by 1';
  EXECUTE immediate l_sql;
  COMMIT;
END reset_sequence;
/

--execute procedure to test it out
EXECUTE reset_sequence;

--review results; should be the same
SELECT source_schema.source_seq.nextval@SOURCE_DB, target_seq.nextval FROM dual;