Oracle de Fizzbuzz #1 - いまごろ・・・ですが・・ Tweet

え〜〜っと、前回のエントリで予告していたとおり、あの話題。
今頃。。。という感じのネタですが、少々視点を変えて、fizzbuzzをOracle上で解かせるための簡単な環境作りを中心に。

では、早速、環境作りから。

Oracleではプロファイルによるリソース制限を設定することができる。この機能はOracle7のころから提供されている。（使っている方はどれだけいるか定かでないが。。。）
この機能を利用すれば、セッション単位で、CPU利用時間や、接続時間、アイドル時間などを制限することが可能だ。

ここまで書けば、なにを言いたいか想像できると思う。　

そう、プロファイルを利用すれば、fizzbuzz問題の解答時間制限を設定できるんです！

以下のように、プロファイルで connect_time（接続時間）制限を２分と設定しておけば、接続から２分強経過した時点でオラクルとのセッションが強制的にdisconnectされる。

fizzbuzzユーザに接続して、２分程度でfizzbuzzを解いてみて！？　という環境が簡単に作れるわけです。

この制限は、SQL*Plusから接続しても、
iSQL*Plusから接続しても、Oracle SQL Developer、JDeveloperから接続しても同じなので、

例えば、

Linux/MacOSX/Windowのクライアントを用意しておき、
好きなクライアントからSQL*Plusを利用して、SQL又は、PL/SQLでfizzbuzzを解いて！　とか

Linux/MacOSXのクライアントからSQL*plusを利用して、SQL又は、PL/SQLでfizzbuzz解いて！　
ただし、editコマンドで起動するのはvi　　とか

前提条件を絞って、SQLだけで、fizzbuzz解いて！　とか、　

データベースサーバーとして、Oracle9iを用意しておいて、SQLだけで、fizzbuzz解いて！　とか

nullカラムで且つ、１０行登録されている表を使って、fizzbuzz解いて！

ということを制限時間内に行わせてみる環境が作れるわけです。

（こんなこと考えていると、PL/SQL で Python Challengeをやっていたことを思いだす。。。。。）

注）
コネクションプーリングしているユーザにconnect_timeとか、idle_timeなんて制限付けたりしないでくださいね！。
そんなことする方は居ないと思うけど。。念のため。

以下、fizzbuzzというプロファイルを作成（接続時間制限２分）、fizzbuzzユーザを作成し、fizzbuzzプロファイルを設定。
最後に、初期化パラメータ、 resource_limitをtrueにしてあげれば、準備完了。　という例です。


尚、作成するユーザには、必要最小限のシステム権限やオブジェクト権限だけを付与おくといいですね。例えば、表やシーケンスを作成できないようにしておくとか。

SYS> show parameter resource_limit 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
SYS> 
SYS> create profile fizzbuzz limit connect_time 2;

プロファイルが作成されました。

SYS> select * from dba_profiles where profile='FIZZBUZZ';

PROFILE    RESOURCE_NAME                    RESOURCE LIMIT
---------- -------------------------------- -------- ----------
FIZZBUZZ   COMPOSITE_LIMIT                  KERNEL   DEFAULT
FIZZBUZZ   SESSIONS_PER_USER                KERNEL   DEFAULT
FIZZBUZZ   CPU_PER_SESSION                  KERNEL   DEFAULT
FIZZBUZZ   CPU_PER_CALL                     KERNEL   DEFAULT
FIZZBUZZ   LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
FIZZBUZZ   LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
FIZZBUZZ   IDLE_TIME                        KERNEL   DEFAULT
FIZZBUZZ   CONNECT_TIME                     KERNEL   2
FIZZBUZZ   PRIVATE_SGA                      KERNEL   DEFAULT
FIZZBUZZ   FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
FIZZBUZZ   PASSWORD_LIFE_TIME               PASSWORD DEFAULT
FIZZBUZZ   PASSWORD_REUSE_TIME              PASSWORD DEFAULT
FIZZBUZZ   PASSWORD_REUSE_MAX               PASSWORD DEFAULT
FIZZBUZZ   PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
FIZZBUZZ   PASSWORD_LOCK_TIME               PASSWORD DEFAULT
FIZZBUZZ   PASSWORD_GRACE_TIME              PASSWORD DEFAULT

16行が選択されました。

SYS> 
SYS> create user fizzbuzz identified by jazz    
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users
  5  profile fizzbuzz;

ユーザーが作成されました。

SYS> grant create session,create procedure to fizzbuzz;            

権限付与が成功しました。

SYS> 
SYS> alter system set resource_limit = true scope=both;

システムが変更されました。

SYS> 

fizzbuzzユーザにconnectして、モタモタしていると・・・・・・。Oracleさんからdisconnectされちゃいます！

FIZZBUZZ> select 'Zzzzzz......' from dual:
select 'Zzzzzz......' from dual
*
行1でエラーが発生しました。:
ORA-02399: 最大接続時間を超えました。ログオフ中です。


FIZZBUZZ>

もし、他の前提条件なしで、Oracle9i以降の環境を利用してfizzbuzz解いて！　といきなり言われたとしたら、私なら、linuxのクライアントから以下のような無名PL/SQLブロックだけで解くだろうな〜。
（無難な方法だし、１分もかからないでできるだろう。。ただ、SQLだけで、Oracle9iという前提だと、１分強かもしれないな〜。まず、どうんな方法で１００行準備するかを決めなきゃならないからね）

FIZZBUZZ> set serveroutput on 
FIZZBUZZ> l
  1  begin
  2    for i in 1..100 loop
  3      dbms_output.put_line(
  4       case when mod(i,15)=0 then 'fizzbuzz'
  5       when mod(i,3)=0 then 'fizz'
  6       when mod(i,5)=0 then 'buzz'
  7       else to_char(i)
  8       end
  9      );
 10    end loop;
 11* end;
FIZZBUZZ> /
1
2
fizz
4
・・・・中略・・・・
98
fizz
buzz

PL/SQLプロシージャが正常に完了しました。

FIZZBUZZ> 
経過: 00:00:00.05

次回は、幾つかの前提条件を設けて、Oracle de fizzbuzz　する予定（そんなにfizzbuzzネタで引っ張るつもりはないですよ。w）