シーケンスの値を再設定する
シーケンスの現在値は下の例のように ALTER SEQUENCE で直接は設定することができない。
ALTER SEQUENCE sequence_name START WITH 100 ;
..
ORA-02283: 開始順序番号は変更できません。
しかし、制限付きではあるが増分値に細工をして調整することで 再設定 もどき は可能である。
マニュアル上では、異なる順序番号で再開する場合はシーケンスを再作成するように書かれている。
これはセッションに現在値を保持するために CURRVAL をインスタンス化している状態を破棄することができるという点からも
理にかなっている。
しかし、開発最盛期や大規模テスト中などには関連するオブジェクトが連鎖して INVALID な状態になる(※1)ために DROP 行為を
避けたいケースやキリ番から再テストしたいケースなどが多々ある。
(※1) INVALID なプローシージャなどは、次回呼び出し時には自動コンパイルされるはず(※2)なのであるがレスポンスや INVALID なオブジェクトが散在したままという点も気になる。
かといって、テストや開発作業中にスキーマ全体を一気にコンパイルして、どこかで「あっ！」という言葉が
聞こえてきたりすると、とても心臓に悪い。
(※2) 何度呼び出しても自動コンパイルが行われずエラーになる現象が少なからずあり、神経質になっている人は多いはずである。セッションを再接続するとインスタンス情報が初期化されて解決したりする。そのため実機においては自動コンパイルの機能を積極活用？をしない方がよい。（セーフティネット、保険として使うに留める）
シーケンスの再設定プロシージャ
以下のプロシージャは 増分値が 1 であること、シーケンスが上限値に達していないこと、再設定する値が
MINVALUE + 1 以上 MAXVALUE 以下に含まれることなどが必須である（場合により変わるかも）。そして、正常ケースでしか正しく動作しないし、ロールバックの仕組み も実装していない。
正しいパラメータを指定していない場合にはシーケンスの情報は壊れて台無し状態になる。つまり本番環境で運用するには向いていない。取り扱いには、くれぐれも要注意である。
CREATE OR REPLACE PROCEDURE RIVUS.RESET_SEQUENCE_LIGHT(
P_SEQ_NAME IN VARCHAR2,
P_START_WITH IN NUMBER := 2 -- ここは適当に編集してください
)
IS
vCurrent NUMBER;
vStart NUMBER;
BEGIN
vStart := NVL(P_START_WITH, 2); -- ここは適当に編集してください
-- 修正用基準値の取得
EXECUTE IMMEDIATE
'SELECT ' || P_SEQ_NAME ||'.NEXTVAL FROM DUAL' INTO vCurrent;
-- シーケンスのメタデータの設定
EXECUTE IMMEDIATE
'ALTER SEQUENCE '||P_SEQ_NAME||' INCREMENT BY '||(vStart-vCurrent-1);
-- シーケンスの再設定
EXECUTE IMMEDIATE
'SELECT ' || P_SEQ_NAME ||'.NEXTVAL FROM DUAL' INTO vCurrent;
-- シーケンスのメタデータ(増分値)の復旧
EXECUTE IMMEDIATE
'ALTER SEQUENCE ' || P_SEQ_NAME || ' INCREMENT BY 1';
END;
/
-- シーケンス SAMPLE_SEQ を 10 に再設定する
-- DROP SEQUENCE SAMPLE_SEQ ;
SQL> CREATE SEQUENCE SAMPLE_SEQ ;
順序が作成されました。
SQL> SELECT SAMPLE_SEQ.NEXTVAL FROM DUAL ;
NEXTVAL
----------
1
SQL> CALL RESET_SEQUENCE_LIGHT('SAMPLE_SEQ', 10) ;
コールが完了しました。
SQL> SELECT SAMPLE_SEQ.NEXTVAL FROM DUAL ;
NEXTVAL
----------
10
-- シーケンスの情報を壊す呼び出し例 (MINVALUE と同じ値に再設定を試みる)
--
SQL> CALL RESET_SEQUENCE_LIGHT('SAMPLE_SEQ', 1) ;
CALL RESET_SEQUENCE_LIGHT('SAMPLE_SEQ', 1)
*
行1でエラーが発生しました。:
ORA-08004: 順序SAMPLE_SEQ.NEXTVALのgoes
belowMINVALUEと矛盾しています。インスタンス化できません
ORA-06512: "RIVUS.RESET_SEQUENCE_LIGHT", 行17
ORA-06512: 行1
