Copy the values of a sequence from one Oracle database to another

Question

Is it possible to copy the current values of sequences in a schema to another database? The sequences have already been created in both databases. This is in Oracle.

Edit:

Based on the help below, once the database link is set up, this script will make sure that the target database sequence values are greater than or equal to the source database values. The motivation for this is so that we don't get primary key errors after copying data, so the fact that the target numbers are not exact is no problem.

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

A combination of UltraCommits statements and a database link, in addition to a stored procedure that you can schedule to automatically run, would serve you well.

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

Answer 2

I encountered the following error while attempting to execute a script based on the one provided by Mr. Reynold:

ORA-04013: number to CACHE must be less than one cycle.

Cause: number to CACHE given is larger than values in a cycle. Action: enlarge the cycle, or cache fewer values.

This error is encountered if the current nextval is too large to allow for the specified number of values to be cached in the current cycle. Accordingly, I have included an updated version of his script in which the target sequence is first altered with the "NOCACHE" option, then the cache is restored to it's original value after the target nextval has been updated. In my example the db-link and alias "PD" refers to the source database and "QA" refers to the target database.

I hope this is as helpful to someone else as the previous solution was to me.

SET serveroutput ON
DECLARE
   CURSOR GetCursorsToSync IS
   SELECT pd.sequence_name, pd.last_number last_number_pd,
          qa.last_number last_number_qa, qa.cache_size
     FROM user_sequences@PD pd
     JOIN user_sequences qa
       on qa.sequence_name = pd.sequence_name
    WHERE qa.last_number != pd.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,
      cache_size    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);
      IF cache_size > 0 THEN
         l_sql := 'alter sequence '|| sequence_name || ' nocache';
         dbms_output.put_line(l_sql);
         EXECUTE IMMEDIATE l_sql;
      END IF;

      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;

      IF cache_size > 0 THEN
         l_sql := 'alter sequence '|| sequence_name || ' cache ' || TO_CHAR(cache_size);
         dbms_output.put_line(l_sql);
         EXECUTE IMMEDIATE l_sql;
      END IF;

      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_pd,
         CursorsTable(i).last_number_qa, CursorsTable(i).cache_size);
      i := CursorsTable.NEXT(i);
   END LOOP;
END;
/