Oracle関連備忘録（開発編）

バッチファイルからsqlファイルを実行する

@echo off
sqlplus -s userid/password @"C:\temp\xxxx.sql"

ディレクトリを削除・作成する

DROP DIRECTORY DMPDIR;
CREATE DIRECTORY DMPDIR AS 'C:\DUMP';

キャッシュのクリア

　わざわざサーバーを再起動しなくてもクリアできる、パフォーマンスの検証時に有効。
　・共有プール（SHARED POOL）をクリアする
　　ALTER SYSTEM FLUSH SHARED_POOL;
　・データベース・バッファ・キャッシュをクリアする
　ALTER SYSTEM FLUSH BUFFER_CACHE ;
　

WITH句を活用する

WITHを使うと、SQLの重複を避けることができ、SQLが見やすくなる。

Oracleへの疎通確認を行う

http://miniminiadmin.jugem.jp/?eid=643
tnspingとか

接続を確認する

V$SESSIONで確認する。
JavaでDriverManager.getConnectionでコネクションを取得すると、V$SESSIONにレコードが追加されていることが分かる。

MERGE文のサンプル

--ON句で、一意にならないと、エラーになるので注意
 merge into AAAAA A
 USING BBBBB B
 ON (A.PATTERN_ID = B.PATTERN_ID)
 when matched then
 update set
 A.PATTERN_NAME = 'aaa'
 when not matched then
  insert (
 PATTERN_ID,
 PATTERN_NAME,
 ) values (
 B.PATTERN_ID,
 B.PATTERN_NAME
 );

UNIONとUNION ALLの違い

UNIONは、上セレクトと下セレクトでヒットしたデータ全て。(重複含まない)

 select * from AAA where COL = '0001'
 union
 select * from AAA where COL = '0002'

UNION ALLは、上セレクトと下セレクトでヒットしたデータ全て。(重複含む)

 select * from AAA where COL = '0001'
 union all
 select * from AAA where COL = '0002'

DECODEとCASE文の違い

DECODE関数とCASE文の場合、動作に違いが出る。
CASE文だと、NULLを評価出来ない為、結果が異なってしまうので要注意。

アナライズについて

-- 全テーブルに関する情報が得られる。(アナライズの実施時期など)
 select * from all_tables;

アナライズコマンドの実施方法
対象のスキーマにアクセス出来る権限でログインする。
スキーマ名とテーブル名のところを、アナライズを掛けたいテーブルに変更する。
複数テーブルがある場合には、BEGINとENDの間に、行を追加すればよい。

BEGIN
      -- 引数に指定されたテーブルの統計情報を取得する
      DBMS_STATS.gather_table_stats('スキーマ名','テーブル名',estimate_percent=>   DBMS_STATS.AUTO_SAMPLE_SIZE);
 end;

TNSの設定

C:\Oracle\XXXX\NETWORK\ADMIN
・tnsnames.ora
識別子(TNS名)=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =ホスト名)(PORT = ポート名))
)
(CONNECT_DATA =
(SERVICE_NAME = サービス名)
)
)

オプティマイザモードの確認

CHOOSEだとコスト、それ以外はルールベースを表す

select value from v$parameter where name = 'optimizer_mode'; 

特殊文字について

・CRを表すのはchr(13)
・LFを表すのはchr(10)
　chr(10)やchr(13)は、文字列ではないので、''で囲わないことに注意。

・%は任意の文字列（ワイルドカード文字）
・_は任意の1文字（ワイルドカード文字）

古いOracle製品（9iとか）について

　レジストリにけっこうゴミが残って、インストール・アンインストール周りでトラブる

INDEXについて

INについて

A.COLUMN = '1A00'とA.COLUMN in ('1A00')は同じレスポンスである。
単一指定の場合は問題ないが、in ('1A00','2A00')というように複数指定した場合、
数が多くなるごとに、レスポンスは悪くなる。
A.COLUMN in ('1A00','2A00')と(A.COLUMN= '1A00' or = '2A00')は同じレスポンスである。

Oracle.DataAccess.dllを使うときに、以下エラーが発生する

the provider is not compatible with the version

Visual Studioの「参照設定」で、「Oracle.DataAccess」で以下のように設定する。
ローカルコピー　True
特定バージョン　False

exeを配布すると、OracleClientのバージョンに依存して、端末によってエラーになる。
exeと同じフォルダに以下も一緒に格納すると、これらのdllが使われるので端末に依存しなくなる。
OraOps11w.dll
Oracle.DataAccess.dll