Oracleのシーケンス(sequence)オブジェクトを変更する3つのやりかた

  • Oracleシーケンスの値を変更したい
  • Alterでやろうと思ったら出来ないじゃん!

別の記事で、Oracleシーケンスオブジェクトの基本的な使い方をまとめましたが、今回はシーケンス(sequence)の現在値を変更する方法です。  

開発中にデータを手修正なんかしていると、シーケンスの値がDBの値と不整合を起こして、キー重複しちゃうなんてことはよくあることだと思います。

そこで、シーケンスの値を変更(更新)したかったのでやり方を調べてみました。  

ざっと考えたり調べた結果、シーケンス(sequence)を変更するには3通りのやり方ができそうなので、その方法と個人的なオススメの方法を紹介します。  

この記事でわかること
  • Oracleシーケンスオブジェクトの変更方法
目次

Oracleのシーケンス(sequence)オブジェクトを変更する3つのやりかた

Oracleのシーケンス(sequence)の値を変更するには以下の3つの方法があります。  

  1. Drop,Createで作り直して変更する  
  2. nextvalで進めて変更する  
  3. Alterとnextvalを駆使して変更する  

と、まぁこんな感じなんですが、  各々メリットデメリットがあるので、ケースバイケースで使い分ければいいと思います。  

それでは1つずつ説明していきます。

シーケンスをDrop,Createで作り直して変更

これはそのまんまですが、シーケンス(sequence)オブジェクト自体をいったん削除して、Create文のSTART WITHパラメータに変更したい現在値を設定して作り直しちゃえって方法です。

作り直して変更するメリット

・明示的に設定値を指定できるので、わかり易い
・最小SQL2発で済む

 作り直して変更するデメリット

・sequenceに関する権限とか設定しないといけない
・シノニム等の設定も同様に再度しないといけない
・DDL文を実行することによる影響が怖い

 シーケンスのDrop,Create[sample]

Oracleのシーケンス(sequence)オブジェクトの削除方法、作成方法はこちらの記事に書きましたのでここでは省略。  

オーカミ

細かい設定がないDB環境ならばこの方法もありかな?

オーカミ

でも現実はそんなことは滅多にないと思うので、あまり効率的ではないかな・・・。

シーケンスをnextvalで進めて変更

この方法はnextvalでシーケンス値を進めちゃって値を変更させる方法です。  

シーケンス値が100だけど、DBの主キーが5000になってるって場合なんかは使えると思います。  

進めて変更するメリット

・DDL文を使わないで対応できる
・1つのSQL文で対応可能
・原始的だからわかり易い

進めて変更するデメリット

・大量に進めたい場合(100 ⇒ 10000000)にしたい場合などは時間がかかる
・sequenceのINCREMENT BYに設定されている増減値で進めることしかできない(戻せない)

シーケンスのnextval[sample]  

ex:現在値10のシーケンスを5000に進める

オーカミ

nextvalを4990回実行すれば良いね!

select
    {SEQオブジェクト}.nextval
from
    dual
connect by
    level <= 4990;

このSQLではCONNECT BY 句を使って再帰的処理にしてます。

この方法は至って原始的な方法ですが、ちょっと進めれば問題解決できる場合なんかは効率的です。

ただし、デメリットにあるように、現在100を20に戻したい場合なんかは使用できません。  

また、大幅に進めたい場合も時間がかかります。  

シーケンスをAlterとnextvalで変更する方法

この方法はAlterを使ってシーケンス(sequence)オブジェクトのINCREMENT BYの値を変更し、そのあとnextvalでシーケンス値を進めた後に、再度AlterでINCREMENT BYを元に戻すという方法です。  

そうすることで、シーケンスの現在値を任意の値まで進め、事実上シーケンスの値を変更した結果となります。  

Alterで変更するメリット

・ほぼ値を指定しての設定が可能
・1.のように権限、シノニムの再設定が必要なし
・2.のように大量の移動も時間がかからない
・値を増やすも減らすも自分次第

Alterで変更するデメリット

手順が3SQLを実行する必要がある
指定したい値と現在値の差分を取ってSQLを作成する必要がある
AlterだけどDDL実行する
最後にINCREMENT BYを戻すの忘れると惨事
作業中にほかの人がnextvalを行われると・・・

シーケンスのAlter/nextval[sample]

ex)現在値10のシーケンスを7777777777に進める場合

Step1

シーケンスのINCREMENT BYを設定したい値と現在値の差分に変更する。

alter sequence
    {SEQオブジェクト}
increment by 7777777767;

777777767 は 7777777777-10 で算出

Step2

nextvalを1回実行する

select
    {SEQオブジェクト}.nextval
from
    dual
;

Step3

INCREMENT BYを元の値に戻す。  

alter sequence
    {SEQオブジェクト}
increment by 1;

んな感じで差分をシーケンスオブジェクトのINCREMENT BYに設定し、1回進めて目的の値にした後に、また元に戻すという感じです。  

これだと現在値を減らすことも可能ですし、シノニムの再設定の必要もありません。  

オーカミ

デメリット多いけれど、いちばん何でもできる方法だね!

Oracleのシーケンス変更のまとめ

Oracleのシーケンス(sequence)オブジェクトを変更する3つの方法をまとめてみました。

オーカミ

個人的なオススメは①と②が良いな

ちょっと進めるだけなら②の方法が一番だし、戻す必要があったり、大量に進めるときは①の方法みたいな使い分けすればいいと思います。  

誰かの参考になれば幸いです。 

まとめ
  • Oracleのシーケンス変更のやり方は3つあるけれど、どのやり方が良いかはケースバイケース!
  • 個人的におすすめはDropCreateで作り直すか、nextvalで進めちゃう方法!
よかったらシェアしてね!

コメント

コメントする

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)

目次
閉じる