タグ「Oracle」が付けられているもの

Oracleでデータを洗い替え

  • 投稿日:
  • カテゴリ:

洗い替えって言葉は普通に使う言葉なのかなと疑問に思いつつも、今回は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

 

ちょっと釣りみたいなタイトルです。

LOBに関してはOracleが分厚いPDFファイルを用意しているので、そちらを参照して理解するのが良いと思います。今回はCLOBへアクセスする速度が速くなるわけではなく、いかにCLOBへアクセスさせないようにする方法です。アクセス速度自体はchunkのサイズとか気にすれば良いと思います。

そもそもなぜCLOBを使うのか?

これはVARCHAR2が4000バイトまでのため、AL32UTF8のデータベースの場合、日本語は最大4バイト消費します。(マニュアルによると、4バイトのUTF8は内部で6バイト使うみたいな事が書いてありましたので、ちょっと検証する必要はありそうです。)
ちなみに全角文字が2バイトという神話はSJISやEUC(一部3バイトですが)の時代の話です。UTF8では半角カナや漢字は3バイト、一部の漢字は4バイト使います。

さて、CLOBに入れるデータは常に4000バイト以上とは限りません。4000バイトで収まる場合はVARCHAR2のカラムに入れて収まらない場合はCLOBのデータに入れれば良いと思います。つまり1000バイトの文字列であればCLOBに入れる必要は無いわけです。今回はぱぱっと対応する策(松竹梅で言う梅コース)でどれほど効果があるか確認してみました。

COMMENT_TEXTがCLOBの列です。梅コースのためVARCHAR2の列はありません。LENGTH関数はCLOBに対しては文字数を返します。(LENGTHBはCLOBには適用できません)。CLOB中にはマルチバイト文字が2バイトで入っているという噂なので(UCS2で保存してるの??) 2000で判定しています。(サロゲート文字については未検証)。TO_CHARでVARCHAR2に変換しています。

SELECT
    CASE
        WHEN LENGTH(COMMENT_TEXT) > 2000 THEN NULL
        ELSE TO_CHAR(COMMENT_TEXT)
    END AS COMMENT_TEXT_A,
    CASE
        WHEN LENGTH(COMMENT_TEXT) > 2000 THEN COMMENT_TEXT
        ELSE NULL
    END AS COMMENT_TEXT_B
FROM DIARY_COMMENT

上記のSQLを実行すると1014レコード帰ってきます。
全部CLOBとして取得する場合は3120msかかりますが、上記のSQLでは125ms程度になりました。
※CLOBとしては2レコードしかありませんでした

ものによりますが、4000バイト以上使う割合が少ないものではこういうやり方もありなのではないかと思います。

最速はおそらくVARCHAR2(4000)の列をいくつか作って分割するという方法とかあると思います。設計が美しくないですけど。。。

行値構成子(行値式)

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

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

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))

 

Oracleでランダムシーケンサーの作成 続編

  • 投稿日:
  • カテゴリ:

作ってみたものの、ストプロの中で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);

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

Oracleでランダムシーケンサーの作成 準備編

  • 投稿日:
  • カテゴリ:

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

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

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

 

Oracle11gの認証とObjectBrowser

  • 投稿日:
  • カテゴリ:

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

以下のURLを参照。

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

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

Commons-DBCP

  • 投稿日:
  • カテゴリ:

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ドライバに入ってるコネクションプーリング実装は、
「百害あって一利なし」
ということが判明しました。
それはさておき、簡単なサンプルコードは以下になります。