【Oracle】シーケンスの削除・作成・変更方法（DROP / CREATE / ALTER SEQUENCE）

シーケンスの特徴

• テーブルとは独立したデータベースオブジェクト
• 複数のテーブル・セッションから同時アクセス可能
• 採番はギャップ（欠番）が発生する場合がある（ロールバック時など）
• 主キーやユニークIDの生成に最適
• MySQLの AUTO_INCREMENT とは異なり、テーブルに紐付かない

DROP SEQUENCE [スキーマ名.]シーケンス名;

ポイント：DROP SEQUENCE はDDL文のため、実行と同時に暗黙的にCOMMITされます。ROLLBACK で取り消すことはできません。

-- シーケンスを削除
DROP SEQUENCE emp_seq;

-- スキーマを指定して削除
DROP SEQUENCE hr.emp_seq;

実行結果

Sequence dropped.

-- シーケンスを参照しているオブジェクトを確認
SELECT name, type, referenced_name
FROM user_dependencies
WHERE referenced_name = 'EMP_SEQ'
  AND referenced_type = 'SEQUENCE';

注意：シーケンスを削除しても、そのシーケンスを参照しているトリガーやプロシージャは自動削除されません。参照元オブジェクトは INVALID（無効）状態になり、次回実行時にエラーが発生します。

-- 存在する場合のみ削除（エラーを無視）
BEGIN
  EXECUTE IMMEDIATE 'DROP SEQUENCE emp_seq';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2289 THEN
      RAISE;
    END IF;
END;
/

Oracle 23c以降の新構文

• Oracle 23cから DROP SEQUENCE IF EXISTS 構文が使えます
• 例: DROP SEQUENCE IF EXISTS emp_seq;
• 23c未満ではPL/SQLの例外処理で代替してください

-- 自分が所有するシーケンスの一覧と設定を確認
SELECT sequence_name,
       min_value,
       max_value,
       increment_by,
       last_number,
       cache_size,
       cycle_flag,
       order_flag
FROM user_sequences
ORDER BY sequence_name;

実行結果

SEQUENCE_NAME  MIN_VALUE  MAX_VALUE             INCREMENT_BY  LAST_NUMBER  CACHE_SIZE  CYCLE_FLAG  ORDER_FLAG
-------------  ---------  --------------------  ------------  -----------  ----------  ----------  ----------
DEPT_SEQ               1  9999999999999999999              1          100          20  N           N
EMP_SEQ                1  9999999999999999999              1         1050          20  N           N
ORDER_SEQ              1  9999999999999999999             10            1          20  N           N

注意：LAST_NUMBER は「次に払い出される値」ではなく、「次にキャッシュされる先頭の値」です。現在の採番値を正確に知りたい場合は シーケンス名.CURRVAL を使用してください。

-- 他スキーマのシーケンスも含めて確認
SELECT sequence_owner,
       sequence_name,
       increment_by,
       last_number
FROM all_sequences
WHERE sequence_owner = 'HR'
ORDER BY sequence_name;

-- 特定のシーケンスが存在するか確認
SELECT COUNT(*) AS seq_exists
FROM user_sequences
WHERE sequence_name = 'EMP_SEQ';

CREATE SEQUENCE [スキーマ名.]シーケンス名
  [START WITH     初期値]
  [INCREMENT BY   増分値]
  [MINVALUE       最小値 | NOMINVALUE]
  [MAXVALUE       最大値 | NOMAXVALUE]
  [CACHE          キャッシュ数 | NOCACHE]
  [CYCLE        | NOCYCLE]
  [ORDER        | NOORDER];

-- 最もシンプルなシーケンス（デフォルト設定）
CREATE SEQUENCE emp_seq;

実行結果

Sequence created.

-- 実務でよく使うシーケンス設定
CREATE SEQUENCE order_seq
  START WITH     1000
  INCREMENT BY   1
  MINVALUE       1000
  MAXVALUE       9999999
  CACHE          50
  NOCYCLE
  NOORDER;

-- 次の連番を取得
SELECT emp_seq.NEXTVAL FROM dual;

-- INSERT文で使用（最も一般的な使い方）
INSERT INTO employees (emp_id, emp_name, email)
VALUES (emp_seq.NEXTVAL, '山田太郎', 'yamada@example.com');

実行結果（SELECT）

   NEXTVAL
---------
        1

-- 先にNEXTVALを呼び出す（必須）
SELECT emp_seq.NEXTVAL FROM dual;

-- 現在のセッションで最後に取得した値を確認
SELECT emp_seq.CURRVAL FROM dual;

注意：セッション開始後に一度も NEXTVAL を呼び出していない状態で CURRVAL を参照すると、ORA-08002: sequence EMP_SEQ.CURRVAL is not yet defined in this session エラーが発生します。

-- パターン1: 親子テーブルへの同時INSERT（CURRVALで同じIDを使う）
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (order_seq.NEXTVAL, 101, SYSDATE);

INSERT INTO order_details (detail_id, order_id, product_id, qty)
VALUES (detail_seq.NEXTVAL, order_seq.CURRVAL, 501, 2);

-- パターン2: PL/SQLで変数に格納して使い回す
DECLARE
  v_id NUMBER;
BEGIN
  v_id := emp_seq.NEXTVAL;
  INSERT INTO employees (emp_id, emp_name) VALUES (v_id, '鈴木一郎');
  INSERT INTO emp_history (emp_id, action) VALUES (v_id, 'CREATED');
END;
/

ALTER SEQUENCE シーケンス名
  [INCREMENT BY   新しい増分値]
  [MINVALUE       新しい最小値]
  [MAXVALUE       新しい最大値]
  [CACHE          新しいキャッシュ数 | NOCACHE]
  [CYCLE        | NOCYCLE]
  [ORDER        | NOORDER];

注意：ALTER SEQUENCE では START WITH は変更できません。開始値を変えたい場合は、シーケンスを DROP して再作成するか、後述のリセット方法を使用してください。

-- 増分値を変更
ALTER SEQUENCE emp_seq INCREMENT BY 5;

-- 最大値を変更
ALTER SEQUENCE emp_seq MAXVALUE 100000;

-- キャッシュサイズを変更
ALTER SEQUENCE emp_seq CACHE 100;

-- 循環を有効にする
ALTER SEQUENCE emp_seq CYCLE;

実行結果

Sequence altered.

-- 手順1: シーケンスを削除
DROP SEQUENCE emp_seq;

-- 手順2: 同じ名前で再作成（START WITHで開始値を指定）
CREATE SEQUENCE emp_seq
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

注意：シーケンスを参照しているトリガーやプロシージャがある場合、DROP後にそれらは INVALID になります。再作成後に ALTER TRIGGER トリガー名 COMPILE; などで再コンパイルしてください。

-- 現在値が1050だとして、1にリセットしたい場合

-- 手順1: 現在値を確認
SELECT emp_seq.NEXTVAL FROM dual;  -- 結果: 1050

-- 手順2: 増分値を「-(現在値 - 目標値)」に設定
ALTER SEQUENCE emp_seq INCREMENT BY -1049;

-- 手順3: NEXTVALで目標値まで戻す
SELECT emp_seq.NEXTVAL FROM dual;  -- 結果: 1（1050 + (-1049) = 1）

-- 手順4: 増分値を元に戻す
ALTER SEQUENCE emp_seq INCREMENT BY 1;

-- 初期値（START WITH値）にリセット
ALTER SEQUENCE emp_seq RESTART;

-- 指定した値からリスタート
ALTER SEQUENCE emp_seq RESTART START WITH 500;

ポイント：Oracle 18c以降を使っている場合は、RESTART が最も簡単で安全なリセット方法です。DROPせずに権限やGRANT設定もそのまま維持できます。

-- MySQLでの連番生成（AUTO_INCREMENT）
CREATE TABLE employees (
  emp_id   INT AUTO_INCREMENT PRIMARY KEY,
  emp_name VARCHAR(100) NOT NULL
);

-- AUTO_INCREMENTのリセット
ALTER TABLE employees AUTO_INCREMENT = 1;

-- エラー発生例
SELECT nonexistent_seq.NEXTVAL FROM dual;
-- ORA-02289: sequence does not exist

-- 対処1: シーケンスの存在を確認
SELECT sequence_name
FROM user_sequences
WHERE sequence_name = 'NONEXISTENT_SEQ';

-- 対処2: 他のスキーマの場合はスキーマ名を指定
SELECT hr.emp_seq.NEXTVAL FROM dual;

-- 対処3: シノニムを確認
SELECT synonym_name, table_owner, table_name
FROM user_synonyms
WHERE synonym_name = 'EMP_SEQ';

-- MAXVALUE到達でエラー
-- ORA-08004: sequence SMALL_SEQ.NEXTVAL exceeds MAXVALUE

-- 対処1: MAXVALUEを増やす
ALTER SEQUENCE small_seq MAXVALUE 999999999;

-- 対処2: 最大値の制限を撤廃
ALTER SEQUENCE small_seq NOMAXVALUE;

-- 対処3: 循環を有効にする（用途に応じて）
ALTER SEQUENCE small_seq CYCLE;

CACHEの使い分け

• CACHE 20（デフォルト）：通常のOLTP処理に適切
• CACHE 100〜1000：大量INSERT（バッチ処理）時のパフォーマンス向上
• NOCACHE：欠番を最小限にしたい場合（ただしパフォーマンスが低下）
• キャッシュされた値はインスタンス障害時に失われ、欠番の原因になります

-- 他のユーザーにシーケンスの使用を許可
GRANT SELECT ON emp_seq TO app_user;

-- シノニムを作成して他スキーマからシンプルにアクセス
CREATE SYNONYM emp_seq FOR hr.emp_seq;

-- 権限を取り消す
REVOKE SELECT ON emp_seq FROM app_user;

-- IDENTITYカラム（12c以降推奨）
CREATE TABLE employees (
  emp_id   NUMBER GENERATED ALWAYS AS IDENTITY,
  emp_name VARCHAR2(100) NOT NULL
);

-- オプション付きIDENTITY
CREATE TABLE orders (
  order_id NUMBER GENERATED BY DEFAULT AS IDENTITY
    (START WITH 1000 INCREMENT BY 1),
  customer_id NUMBER NOT NULL
);