なぜ、シーケンス（順序）の番号が飛ぶのか
Oracle のシーケンスは、一意の値を作成するのに便利機能なのですが、様々な理由で欠番が発生することがあります。欠番が発生する理由には、「値を取得したけど使わなかった」とか「共有プールのキャッシュが消えた」などがあるようです。
なぜ欠番が発生するのか
デーベースの再起動
シーケンスには、パフォーマンスを向上させるためキャッシュという機能が用意されています。他の原因でもこのキャッシュが問題となるのですが、シーケンスにキャッシュを指定すると事前にシーケンスから値を取得して共有プールに保持されます。
データベースを再起動すると、共有プールがクリアされてしまうため事前に取得されていた値が欠番となってしまいます。シーケンスに対してキャッシュしないよう定義した場合（ NOCACHE ）、欠番は発生しません。
例えば、次のようなシーケンスを定義したとします。キャッシュを 20 に設定してあるところに注目します。
CREATE SEQUENCE seq_lost_num INCREMENT BY 1 START WITH 1 CACHE 20;
シーケンスから値を取得します。
SELECT seq_lost_num.NEXTVAL FROM DUAL;
NEXTVAL
-----------
1
データベースを再起動後、同じようにシーケンスから値を取得してみます。
SELECT seq_lost_num.NEXTVAL FROM DUAL;
NEXTVAL
-----------
21
キャッシュされた分だけ欠番が発生したことが確認できました。
シーケンスはロールバックしない
シーケンスから一度取得された値は、ロールバックを実行しても元に戻ることはありません。
値を取得して使わなかったり、トランザクション中に Oracle が落ちたりした場合も番号が戻ることはありません。
SELECT seq_lost_num.NEXTVAL FROM DUAL;
NEXTVAL
-----------
1
値を取得した後、ロールバックしてみます。その後、シーケンスから値を取得して確認します。
ROLLBACK;
SELECT seq_lost_num.NEXTVAL FROM DUAL;
NEXTVAL
-----------
2
次の値が返され、ロールバックが影響していないことを確認できました。
共有プールのデータを明示的に消去
シーケンスのキャッシュは共有プールに作成されているため、明示的に共有プールのデータを消去するとシーケンスに欠番が発生します。
ちなみに、共有プールには次のものが格納されています。
- キャッシュされたデータ・ディクショナリ情報
- SQL文の共有SQL領域、共有PL/SQL領域、ストアド・プロシージャ、ファンクション、パッケージおよびトリガー
共有プールを明示的に消去するには、次のように実行します。
ALTER SYSTEM FLUSH SHARED_POOL;
エージアウト
共有プールに作成されたキャッシュは、 長期間使用されずに放置された場合期限切れが発生して共有プールから破棄（エージアウト）されることがあります。この破棄により欠番が発生します。
これを原因とする欠番は、 DBMS_SHARED_POOL パッケージの KEEP プロシージャを使うことによってエージアウトしないようにすることができます。
DBMS_SHARED_POOL は、個別にインストールする必要があるため SqlPlus を使ってインストールします。
% sqlplus / as sysdba
SQL> @?/rdbms/admin/dbmspool.sql
パッケージが作成されました。
権限付与が成功しました。
SQL> grant execute on dbms_shared_pool to SCOTT;
共有プールに常駐させたいシーケンスのスキーマへ接続し、プロシージャを実行します。
SQL> conn SCOTT/パスワード
SQL> call sys.DBMS_SHARED_POOL.KEEP('seq_lost_num', 'Q');
「 v$db_object_cache 」で、共有プールに常駐するよう設定されたことを確認できます。「 KEEP 」列が「 YES 」となっていれば常駐した状態となります。
select owner,name,type,sharable_mem,loads,kept
from v$db_object_cache
where owner = 'SCOTT'
and TYPE = 'SEQUENCE';
OWNER NAME TYPE SHARABLE_MEM LOADS KEEP
------- --------- ---------- --------------- -------- ------
SCOTT seq_lost_num SEQUENCE 4712 2 YES
以上でシーケンスの番号が飛ぶケースが確認できました。