【ORACLE】調整序列的目前種子值
--必須用SYS使用者執行腳本;或具有SYSDBA角色登入;
CREATE OR replace
PROCEDURE seq_reset_startnum(v_seqname varchar2, v_startnum number) AS n number(10);
v_step number(10):=1;--步進
tsql varchar2(200);
BEGIN
EXECUTE immediate 'select '||v_seqname||'.nextval from dual' INTO n;
n:=v_startnum - n - v_step;--從10000001開始
tsql:='alter sequence '||v_seqname||' increment by '|| n;
EXECUTE immediate tsql;
EXECUTE immediate 'select '||v_seqname||'.nextval from dual' INTO n;
tsql:='alter sequence '||v_seqname||' increment by '||v_step;
EXECUTE immediate tsql;
END seq_reset_startnum;
--必須用SYS使用者執行腳本;或具有SYSDBA角色登入;
EXEC seq_reset_startnum('"CONCEPT"."DOCMETADATA_METADATAID_SEQ"', 1110000000);
目前值與最大ID值相差不是太大的可以使用下面腳本:
(注意:如果相差過大,譬如相差1個億,則執行腳本腳本會很耗時)
--重置"CONFIG"."DOCFIELD"序列的目前值
DECLARE
indexnum number;
seqnumber number;
tablenum number;
indexintr number;
BEGIN
SELECT MAX(DOCFIELDID) INTO indexnum FROM "CONFIG"."DOCFIELD";
SELECT "CONFIG"."DOCFIELD_DOCFIELDID_SEQ".nextval INTO seqnumber FROM DUAL;
indexintr := indexnum - seqnumber;
IF indexintr > 0 THEN
FOR ind IN 1..indexintr loop
SELECT "CONFIG"."DOCFIELD_DOCFIELDID_SEQ".nextval INTO tablenum FROM DUAL;
END loop ;
END if;
end;
/