Oracle Archive

洗い替えって言葉は普通に使う言葉なのかなと疑問に思いつつも、今回はEXPしたデータにまるまる入れ替える処理のメモを。

簡単に行いたいのでユーザーを消してしまい関連するオブジェクトを全て削除するという戦略をとりました。表領域がどうなるのかは知りません！！

DROP USER TAMTAM CASCADE;
CREATE USER TAMTAM IDENTIFIED BY "****" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT,RESOURCE TO TAMTAM;
GRANT SELECT ANY DICTIONARY TO TAMTAM;

この後普通にIMPコマンドを実行します。

ちなみにパスワードにドットが入っている場合は""でくくります。

Oracle11gのユーザーのパスワードの有効期限はデフォルトのプロファイルを使っていると180日のようです。

家のPCに入れていた検証用のOraacleに接続できなくてありゃりゃ？と思ったらそういう事でした。（これ会社の足下PCに入れた時にも同じ現象に陥ったような・・・） というわけで気をつけないと。

こちらの記事を参考にしました。
http://blog.goo.ne.jp/bb601410/e/79d22c38edac5054e06756a28ae6933c

今日はまってしまった行値式について。

行値式とはこんな感じのやつです。

WHERE (XXX,YYY) IN ((1,2),(3,4))

何ではまったかと言うと、INの後の()を忘れて IN  (1,2),(3,4)と書いていました。恥ずかしい(´・ω・｀)

製品によっては行値式をサポートしていないらしいので、そういう場合は以下のように無理矢理書いちゃいます。（必ずしもこれがベストってわけではなく、EXISTSつかったり他の方法使った方がたぶん幸せになれます）

WHERE ((XXX = 1 AND YYY = 2) OR (XXX = 3 AND YYY = 4))

作ってみたものの、ストプロの中でINSERT/UPDATE等を発行している場合はそのストプロはSELECT文の中で呼び出せないという制約があるらしいです。

重複チェック用のテーブル作成 

CREATE TABLE RANDOM_SEQUENCE (
    ID VARCHAR2(64),
    RNDSEQ VARCHAR2(128),
    CONSTRAINT PK_RANDOM_SEQUENCE PRIMARY KEY (ID, RNDSEQ)
);

ストプロ作成 

CREATE OR REPLACE FUNCTION rndseq_next (id2 varchar2, opt char, len NUMBER)
    RETURN VARCHAR2 PARALLEL_ENABLE
    IS
        cnt NUMBER := 0;
        rndseq2 VARCHAR2(128) := NULL;
    BEGIN
        LOOP
            rndseq2 := random_string(opt, len);
            SELECT COUNT(*) INTO cnt FROM RANDOM_SEQUENCE WHERE ID = id2 AND RNDSEQ = rndseq2;
            IF cnt = 0 THEN
                EXIT;
            END IF;
        END LOOP;
        INSERT INTO RANDOM_SEQUENCE(ID, RNDSEQ) VALUES (id2, rndseq2);
        COMMIT;
        RETURN rndseq2;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN NULL;
    END rndseq_next;
/

後はこんな感じで呼び出します。

rndseq_next('myid1', 'z', 10);

シーケンサーっぽく使おうと思ったけどダメぽ。。

ユニークなIDを生成する話なんですが、連番で生成すると会員数というか規模がばれてしまいます。

普通は乱数を生成したり、シーケンス番号からHash値を求めたりしてると思いますが、今回はOracleを使ったランダムシーケンサーを実装してみたいと思います。今回はその準備編ということで必要な関数を作ってみました。
作ったと言っても、DBMS_RANDOMパッケージの実装の拡張です。大小英数と数字バージョンが無かったので。

後は重複チェックが必要です。
重複チェックをするにはテーブルが必要になるわけですが、実際に主キーとして使っているテーブルを捜査するのか、それとも専用のテーブルを用意するのかが悩みどころです。今回は専用のテーブルを用意する予定です。
重複チェックもする関数を作ったら完成ですが、それは次回ということで。

Continue reading

Oracle11gにObjectBrowserからログインできない・・。
他のアプリからはログインできるので調査したところ、11では認証が厳しくなったようです。パスワードの大文字小文字を区別するようになりました。って、今までしてなかったのか・・。
そして、ObjectBrowserってもしかして大文字でパスワードを送信してる？ヽ(`Д´)ﾉ

以下のURLを参照。

• http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/network.111/E05730-02/authentication.htm#CHDGHHAD
• http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/network.111/E05730-02/authentication.htm#DBSEG3225|Enabling or Disabling Password Case Sensitivity

DBA_USERSというビューのPASSWORD_VERSIOONSに11Gが含まれていて、かつignorecaseがnの場合に区別されるようです。システム全体、またはユーザー毎に区別できるそうです。

セキュリティ上好ましくないけど、てっとり早くシステム全体で今まで通り区別しないとするには、
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

Commons-DBCPの使い方です。

Commons-DBCPはデータベースのコネクションプールとPreparedStatementのキャッシュをしてくれます。TomcatやStruts1, Springなど有名なアプリケーションサーバやフレームワークが採用しているライブラリですが、これを直接使ってみたいと思います。

ちなみに、こんな記事を見つけました。 かなり前の内容のようなので現状はわかりませんが・・・。

そもそも、OracleのJDBCドライバには、
適切な(?)ファイナライザが実装されていないらしく、
Connectionにしても、Statementにしても、ResultSetにしても、
closeし忘れるとメモリリークが起こるという不具合というか仕様があるらしいです。

でもって、Oracleのコネクションプーリング実装でclose忘れされた日には、
どうにも開放する手段がなく、メモリリークしてしまいます。

その辺りを確かめるべく、
Jakarta Commons DBCPも交えつつ、
色々実験してました。

すると、
・OracleのJDBCドライバをそのまま使用
→ 高負荷時にConnectionを取得できないことがあるが、メモリリークはなし。
・OracleのJDBCドライバのプーリングを使用
→ 高負荷時にConnectionを取得できないことがあり、メモリリークもある。
・Commons DBCP＆OracleのJDBCドライバを使用
→ 高負荷時もConnectionを取得でき、メモリリークはなし。
(ただし、必要最小限のメモリは使用していると思われる。)
という結果が得られました。

というわけで、
OracleのJDBCドライバに入ってるコネクションプーリング実装は、
「百害あって一利なし」
ということが判明しました。

Continue reading