将序列的值从一个 Oracle 数据库复制到另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6424361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy the values of a sequence from one Oracle database to another
提问by Greg Reynolds
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.
是否可以将模式中序列的当前值复制到另一个数据库？序列已在两个数据库中创建。这是在 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;
/
采纳答案by Joel Slowik
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.
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;
回答by user3403231
I encountered the following error while attempting to execute a script based on the one provided by Mr. Reynold:
我在尝试执行基于 Reynold 先生提供的脚本时遇到以下错误：
ORA-04013: number to CACHE must be less than one cycle.
ORA-04013: CACHE 的数量必须少于一个周期。
Cause: number to CACHE given is larger than values in a cycle. Action: enlarge the cycle, or cache fewer values.
原因：给定的 CACHE 数量大于循环中的值。行动：扩大周期，或缓存较少的值。
This error is encountered if the current nextvalis 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 nextvalhas been updated. In my example the db-link and alias "PD" refers to the source database and "QA" refers to the target database.
如果当前nextval太大而无法在当前循环中缓存指定数量的值，则会遇到此错误。因此，我包含了他的脚本的更新版本，其中首先使用“NOCACHE”选项更改目标序列，然后在更新目标nextval后将缓存恢复为其原始值。在我的示例中，db-link 和别名“PD”指的是源数据库，而“QA”指的是目标数据库。
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;
/
回答by Fernando Galves
--
-- Procedure to replicate the value of sequences from source to destination schema
--
-- The database link name must be PROD
--
-- SQL> CREATE DATABASE LINK PROD CONNECT TO ...
--
CREATE OR REPLACE PROCEDURE RESET_SEQUENCES
AS
CURSOR CSEQUENCES
IS
SELECT SEQUENCE_NAME
FROM USER_SEQUENCES@PROD;
L_SEQUENCE_NAME USER_SEQUENCES.SEQUENCE_NAME%TYPE;
L_SRC_SEQ_VALUE USER_SEQUENCES.MAX_VALUE%TYPE;
L_DST_SEQ_VALUE USER_SEQUENCES.MAX_VALUE%TYPE;
L_SQL VARCHAR2(200);
BEGIN
OPEN CSEQUENCES;
LOOP
FETCH CSEQUENCES INTO L_SEQUENCE_NAME;
EXIT WHEN CSEQUENCES%NOTFOUND;
--
-- Select sequence value from source schema
--
L_SQL := 'SELECT '|| L_SEQUENCE_NAME ||'.NEXTVAL@PROD FROM DUAL';
EXECUTE IMMEDIATE L_SQL INTO L_SRC_SEQ_VALUE;
--
-- Select sequence value from destination schema
--
L_SQL := 'SELECT '|| L_SEQUENCE_NAME ||'.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE L_SQL INTO L_DST_SEQ_VALUE;
DBMS_OUTPUT.PUT_LINE('SEQUENCE_NAME: '||L_SEQUENCE_NAME);
DBMS_OUTPUT.PUT_LINE('::BEFORE => SRC VALUE: '||L_SRC_SEQ_VALUE||' DST VALUE: '||L_DST_SEQ_VALUE);
--
-- Synchronize sequences value, only if necessary
--
IF L_SRC_SEQ_VALUE > L_DST_SEQ_VALUE THEN
--
-- Alter increment by to diference between L_SRC_SEQ_VALUE and L_DST_SEQ_VALUE
--
L_SQL := 'ALTER SEQUENCE '|| L_SEQUENCE_NAME ||' INCREMENT BY '||TO_CHAR(L_SRC_SEQ_VALUE-L_DST_SEQ_VALUE);
EXECUTE IMMEDIATE L_SQL;
--
-- Select sequence value
--
L_SQL := 'SELECT '|| L_SEQUENCE_NAME ||'.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE L_SQL INTO L_DST_SEQ_VALUE;
--
-- Alter increment by back to 1
--
L_SQL := 'ALTER SEQUENCE '|| L_SEQUENCE_NAME ||' INCREMENT BY 1';
EXECUTE IMMEDIATE L_SQL;
END IF;
DBMS_OUTPUT.PUT_LINE('::AFTER => SRC VALUE: '||L_SRC_SEQ_VALUE||' DST VALUE: '||L_DST_SEQ_VALUE);
END LOOP;
CLOSE CSEQUENCES;
END RESET_SEQUENCES;
/
--
-- EXECUTE RESET_SEQUENCES PROCEDURE AND BE HAPPY!
--
SQL> EXEC RESET_SEQUENCES
回答by UltraCommit
Question: How do we set the LASTVALUE value in an Oracle Sequence? Answer: You can change the LASTVALUE for an Oracle sequence, by executing an ALTER SEQUENCE command. For example, if the last value used by the Oracle sequence was 100 and you would like to reset the sequence to serve 225 as the next value. You would execute the following commands.
问题：我们如何在 Oracle 序列中设置 LASTVALUE 值？答：您可以通过执行 ALTER SEQUENCE 命令来更改 Oracle 序列的 LASTVALUE。例如，如果 Oracle 序列使用的最后一个值是 100，并且您希望将序列重置为 225 作为下一个值。您将执行以下命令。
alter sequence seq_name increment by 224;
select seq_name.nextval from dual;
alter sequence seq_name increment by 1;
Now, the next value to be served by the sequence will be 225.
现在，序列要提供的下一个值将是 225。
