読者です 読者をやめる 読者になる 読者になる

WHEN NO_DATA_FOUND THEN

Oracle Databaseについてつらつらと。。。

順序を使い果たすにはどのくらいの時間がかかる?

Oracle Databaseにて一意な数値を採番する機能として、順序というものがあります。この順序ですが、実は最大桁数は28桁です。NUMBER型の精度が38桁なのに比べると、10桁も短いです。このことから、順序を大量に採番するとその順序が尽きてしまうのでは、という心配をなさる方もいらっしゃること思います。実際にそういった相談をされたこともあります。ということで、実際に28桁の順序がどの程度もつのか計算してみました。例えば毎秒100億回という、現実的にありえなさそうな回数採番し続けたら何年で順序が尽きるでしょうか?

SQL> select (power(10,28)-1)/(power(10,10)*60*60*24*365) from dual;

(POWER(10,28)-1)/(POWER(10,10)*60*60*24*365)
--------------------------------------------
                              31,709,791,984

ということで、なんと約317億年も耐えられます。うるう年を考慮していないのはご愛嬌ということで。もっとも、うるう年を考慮しても、順序が尽きるという心配をする必要はなさそうです。。。

 

DROP/TRUNCATE TABLEを防ぐ方法

本番データの誤削除したというトラブルがあったようです。このトラブルのように全面削除というのはなかなか聞きませんが、1テーブルのレベルなら伝聞含め聞いたことは多いことかと思います。Oracle Databaseを使っている場合、どうすればこのようなトラブルの発生を防げるかを考えてみたいと思います。

まずはオプション製品を活用する方法です。Enterprise EditionのDatabase Vaultという、標準機能より高度な権限コントロールを行うオプションを導入して、取り決めた管理者の許可がある場合にのみDROP/TRUNCATE TABLEを実施できるように設定します。あるいはDROP/TRUNCATE TABLEを行うためのユーザーを別途作成し、データの操作を行うユーザーではDROP/TRUNCATE TABLEを実施できないように設定します。Database Valutの権限管理機能はかなり柔軟ですので、ほかの重要な操作含め、どのような設計を行うかが肝要です。

次に、オプション製品に頼らない方法です。SE/SE1でもできます。DROP/TRUNCATE TABLE文実行前に起動するトリガーを作成し、そのトリガーの中身を、絶対に例外が発生するようにコーディングします。たとえば以下のようなコーディングにすると、SCOTTスキーマのテーブルをDROPしようとしても常にORA-20001が発生するようになります。実際にはORA-00604も一緒に発生しますが。IF文をやめてRAISE_APPLICATION_ERRORプロシージャの呼び出しだけにすれば、テーブルを含めたすべてのオブジェクトのドロップが対象になります。

CREATE OR REPLACE TRIGGER cancel_drop_table
  BEFORE DROP ON scott.SCHEMA
BEGIN
  IF ora_dict_obj_type = 'TABLE' THEN
    RAISE_APPLICATION_ERROR(-20001, 'Never Drop!!');
  END IF;
END;

実際にテーブルをDROPしたい場合は、先にトリガーをDROPするか、DISABLEにする必要があります。トリガーを別のユーザーで作成して、テーブルのオーナーにそのトリガーのDROP権限がない状態にすれば尚いいですね。

はてなブログにしてみました。

ダイアリーの記事の移行はできるようになったらやりたいと思います。