- Oracleシーケンスの値を変更したい
- Alterでやろうと思ったら出来ないじゃん！
別の記事で、Oracleシーケンスオブジェクトの基本的な使い方をまとめましたが、今回はシーケンス(sequence)の現在値を変更する方法です。
開発中にデータを手修正なんかしていると、シーケンスの値がDBの値と不整合を起こして、キー重複しちゃうなんてことはよくあることだと思います。
そこで、シーケンスの値を変更(更新)したかったのでやり方を調べてみました。
ざっと考えたり調べた結果、シーケンス(sequence)を変更するには3通りのやり方ができそうなので、その方法と個人的なオススメの方法を紹介します。
- Oracleシーケンスオブジェクトの変更方法
Oracleのシーケンス(sequence)オブジェクトを変更する3つのやりかた
Oracleのシーケンス(sequence)の値を変更するには以下の3つの方法があります。
- Drop,Createで作り直して変更する
- nextvalで進めて変更する
- Alterとnextvalを駆使して変更する
と、まぁこんな感じなんですが、 各々メリットデメリットがあるので、ケースバイケースで使い分ければいいと思います。
それでは1つずつ説明していきます。
シーケンスをDrop,Createで作り直して変更
これはそのまんまですが、シーケンス(sequence)オブジェクト自体をいったん削除して、Create文のSTART WITHパラメータに変更したい現在値を設定して作り直しちゃえって方法です。
作り直して変更するメリット
作り直して変更するデメリット
シーケンスのDrop,Create[sample]
Oracleのシーケンス(sequence)オブジェクトの削除方法、作成方法はこちらの記事に書きましたのでここでは省略。
オーカミ細かい設定がないDB環境ならばこの方法もありかな？
オーカミでも現実はそんなことは滅多にないと思うので、あまり効率的ではないかな・・・。
シーケンスをnextvalで進めて変更
この方法はnextvalでシーケンス値を進めちゃって値を変更させる方法です。
シーケンス値が100だけど、DBの主キーが5000になってるって場合なんかは使えると思います。
進めて変更するメリット
進めて変更するデメリット
シーケンスの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で変更するメリット
Alterで変更するデメリット
シーケンスの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)オブジェクトを変更する３つの方法をまとめてみました。
オーカミ個人的なオススメは①と②が良いな
ちょっと進めるだけなら②の方法が一番だし、戻す必要があったり、大量に進めるときは①の方法みたいな使い分けすればいいと思います。
誰かの参考になれば幸いです。
- Oracleのシーケンス変更のやり方は3つあるけれど、どのやり方が良いかはケースバイケース！
- 個人的におすすめはDropCreateで作り直すか、nextvalで進めちゃう方法！
コメント