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

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

编辑：

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

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;
/

解决方法

--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;

（编辑：厦门网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!