Oracle   Oracleちょっとメモ
  2001/01/13 - 2011/12/10

SQL*Plusの「&」
SQL*Plusの変数置換「&」を効かないようにするには、 SET DEFINE OFF を使います。
他の開発ツールで実行できるCREATE文が、SQL*PlusではORA-00933エラーになる
ストアドプロシージャなど以外の「単一のDDL文」の中に空の行が含まれていると、 SQL*Plusでは ORA-00933(SQLコマンドが正しく終了されていません。) エラーになるようです。
アーカイブログモードで、残しておくべきアーカイブログファイルの表示
オンライン・バックアップを行うと、V$BACKUPにバックアップした日付が残るので、 全てのデータファイルのバックアップ日付より古いアーカイブログファイルは削除することができます。 下のSQLは、残しておかなくてはいけないアーカイブログファイルのみを抽出するものです。
SET PAGESIZE 50
SET HEADING OFF
SELECT NAME || ' ' || SEQUENCE# || ' ' || NEXT_CHANGE#
  FROM V$ARCHIVED_LOG
 WHERE NEXT_CHANGE# >= (SELECT MIN(CHANGE#) FROM V$BACKUP
                       WHERE CHANGE# > 0);
データファイルを使用しているDBオブジェクトの検出
データ量が大きく減少して、表領域に空きができた、 というときにはデータファイルのサイズを減らしたいものですが、 所望のサイズを越えるブロックを占有しているデータベースオブジェクトがあってなかなか減らせない、 というようなときに下のSQLが役に立ちます。 まず、DBA_DATA_FILES表を検索して、データファイル名からファイルIDを知ります。 そのうえで、下のようにファイルIDと、所有者や表領域名も使って検索を行います。
SELECT A.SEGMENT_NAME, A.EXTENT_ID, A.FILE_ID
  FROM DBA_EXTENTS A
 WHERE A.OWNER = 'PO' AND A.TABLESPACE_NAME = 'POX'
   AND A.FILE_ID = 22
 ORDER BY A.SEGMENT_NAME
EBCDICコード順のORDER BY
以下のような感じで可能です。これは驚きました。
SELECT ORDER_NO, LINE_NO
  FROM ORDERS
 ORDER BY ORDER_NO, CONVERT(LINE_NO, 'JA16EBCDIC930');
DBMS_SQLパッケージでCREATE文を使うとORA-01031エラー
DBMS_SQLパッケージだと通常PL/SQLでは実行できないDDL文も実行できて便利、 ですが、データベースオブジェクトのDROPはできるのに、 CREATEしようとするとORA-01031エラー(権限が足りません)って? これはDBMS_SQLの仕様で、CREATE文を使う場合には、RESOURCEロールなどのロール経由ではなく、 CREATE SEQUENCE、CREATE TRIGGERなどそのものずばりの権限が必要です。
「PLS-00313: この有効範囲内で***が宣言されていません。」ってなあに?
PL/SQLパッケージやストアドプロシージャなどで内部プロシージャを定義している場合、 コールしている個所よりも上(前)に内部プロシージャの定義がなければこのエラーになります。 下(後ろ)に書いても認識されません。これはPL/SQLの大きな制約です。(9i時点では)
どうしてもプロシージャ定義をコール個所より上に書けない場合は、 「先に宣言だけ書いておく」という方法で回避できます。詳しくは、マニュアルで。
ALTER TABLE 〜 MOVE文の後にビューを参照するとORA-01502エラー
ALTER TABLE 〜 MOVE文を使うと、exp/TRUNCATE/impを行わなくてもセグメントの物理再編成 (最高水位標のリセット)が行え、便利です。 が、その対象表に索引が張られていると、セグメントの移動と同時に索引はUNUSABLE になってしまい、そのままビューなどで検索するとORA-01502エラーが発生します。
そのため、MOVE後は必ず対象表の索引をALTER INDEX 〜 REBUILDで再作成する必要があります。
オブジェクト権限の検索
自分が他のユーザから付与されている権限はUSER_TAB_PRIVS_RECD、 自分が他のユーザに付与している権限はUSER_TAB_PRIVS_MADE というデータディクショナリを検索すると調べることができます。
整数を16進数に変換して文字列に格納する
Oracle8iから使える手を紹介します。
SELECT TO_CHAR(255,'FMXXXX') FROM DUAL;
Xを大文字で書くと16進数のアルファベットが大文字に、 小文字にすると16進数のアルファベットが小文字になります。
0.6は0.6、6は6と表記する
これは少しはまります。というのは、普通に TO_CHAR(0.6)とすると、「.6」という表記になるからです。 「0.6」にしたければ、TO_CHAR(0.6, 'FM999990.999)のようにすればOKですが、 今度は6が「6.」になってしまいます。
小数点が邪魔なんだけどなあ、邪魔だなあ…ではどうするか、 面倒なのでRTRIMすればいいんじゃないでしょうか(おーい)
SELECT RTRIM(TO_CHAR(6,'FM999990.999'), '.') FROM DUAL;
なお、FMをつけずに、代わりに'.0'をRTRIMしても同じ結果が得られます。
表をある列でORDER BYし、先頭の1行だけを取り出す
3つの方法があります。1つ目はポピュラーな
SELECT ID, NAME, AGE FROM EMPS
 WHERE AGE = (SELECT MIN(AGE) FROM EMPS)
こんな感じですね(年齢が最も若い人のレコードを取得)。
ただし、複数の表を結合したりする場合、難しくなってきます。 そのときは2つ目の方法として行番号を振るROW_NUMBER関数を使って
SELECT ID, NAME, AGE FROM (
  SELECT ID, NAME, AGE, ROW_NUMBER() OVER (ORDER BY AGE) RN FROM EMPS
) WHERE RN = 1
このように書けます。
ちなみに、ORDER BYする必要がなく、複数行返る可能性は分かっているが、 それらのどの行を取り出しても問題ないのであればROWNUM擬似列を使うのが分かりやすいです。
SELECT ID, NAME, AGE FROM EMPS WHERE ROWNUM = 1;
但し、ROWNUMはソートする前に連番を振ってしまうので、 どの行でもいいわけではなく、あくまでソートしてから1行目だけを取り出したい場合は、 ORDER BY有りのSQLを副問合せとして、ORDER BY無しのSQLで囲んで二重に書けばOKです。
SELECT ID, NAME, AGE FROM
 (SELECT ID, NAME, AGE FROM EMPS ORDER BY AGE) WHERE ROWNUM = 1;
表をある列でグループ化し、グループごとに別のソートキー列で連番を振る
上と同じ表を使い、所属課別・若い人順に連番を振る例です。
SELECT ID, NAME, AGE,
       ROW_NUMBER() OVER (PARTITION BY DEPT_NO ORDER BY AGE) RN FROM EMPS
これにWHERE句をつけると、「総務部で3番目に若い人は誰か」のような検索が出来ます (あまり現実的でないか)。 もっと実用的な例で言えば、店舗の営業日カレンダーマスタなんかに使えますね。 「ある月の第3営業日は何日か?」のような問合せは在庫管理システムなどでいかにもありそうです。
表を横向きに集計する
「横向きに集計」って何じゃ? うーん、これはなんと表現していいのか分からない表現方法ですが、 企業データベースを運用していると、 利用者に結構よく依頼される集計方法ではあると思います。
ええと、 1つの注文番号で複数のお届け先がありうるとします。
ORDER_NO | OTODOKESAKI
-----------------------------------
00012345 | 横浜市西区南幸z-z-zz
00012345 | 新潟市中央区柳島町z-z-zz
00034567 | 横浜市西区南幸z-z-zz
00034567 | 新潟市中央区柳島町z-z-zz
00034567 | 大阪市淀川区西中島z-z-zz
...
これを
ORDER_NO | お届け先1            | お届け先2                | お届け先3
------------------------------------------------------------------------------------
00012345 | 横浜市西区南幸z-z-zz | 新潟市中央区柳島町z-z-zz | 
00034567 | 横浜市西区南幸z-z-zz | 新潟市中央区柳島町z-z-zz | 大阪市淀川区西中島z-z-zz
SQL1個でこのように集計したいという。 これは次のようにすれば可能です。
SELECT a.ORDER_NO,
       MAX(CASE WHEN a.RN = 1 THEN a.OTODOKESAKI ELSE NULL END) AS OTO1,
       MAX(CASE WHEN a.RN = 2 THEN a.OTODOKESAKI ELSE NULL END) AS OTO2,
       MAX(CASE WHEN a.RN = 3 THEN a.OTODOKESAKI ELSE NULL END) AS OTO3,
       MAX(CASE WHEN a.RN = 4 THEN '他' ELSE NULL END)          AS OTO4
  FROM
(SELECT ORDER_NO, OTODOKESAKI,
        ROW_NUMBER() OVER (
          PARTITION BY ORDER_NO ORDER BY DELIVERY_DATE ) AS RN
   FROM DELIVERY_DB) a
 GROUP BY a.ORDER_NO
ストアドプロシージャの更新日とソースコードを検索する
SELECT b.NAME, b.TYPE, MAX(TO_CHAR(a.LAST_DDL_TIME, 'YYYY/MM/DD HH24:MI:SS')),
       MAX(b.LINE), SUM(LENGTHB(b.TEXT))
  FROM USER_OBJECTS a, USER_SOURCE b
 WHERE a.OBJECT_NAME = b.NAME
   AND a.OBJECT_TYPE = b.TYPE
 GROUP BY b.NAME, b.TYPE
 ORDER BY b.NAME, b.TYPE
あるはずのデータが見れない!
SELECT ITEM_CODE FROM TABLE_A a
 WHERE NOT EXISTS (SELECT 1 FROM TABLE_B WHERE ORDER_NUMBER = a.ORDER_NUMBER)
TABLE_Bに存在しない注文番号をTABLE_Aから探し、その品目コードを表示するという、 超ありがちな例ですが、そのようなデータが存在するはずなのに、 1件も該当しません。なぜでしょうか。
ありがちな例として、TABLE_Aの注文番号がORDER_NUMBER、 TABLE_Bの注文番号がORDER_NOだったとしたら? 「それなら列名が違うからエラーになるだろう」と思ってSQLを再度よく見ると? 「(SELECT 1 FROM TABLE_B WHERE ORDER_NUMBER = a.ORDER_NUMBER)」 の左側のORDER_NUMBERは、当然TABLE_Bの列だろうと読めてしまいますが、 TABLE_BにはORDER_NUMBERという列はないのにエラーにならない? そうです、左も右もORDER_NUMBERはTABLE_Aの同じ列を指しているので、 このSQLでは該当行が現れることはありません。
EXPLAIN PLANで実行計画を見ようとすると 「ORA-01039 insufficient privileges on underlying objects of the view (ORA-01039 ビューのもとになるオブジェクトに関する特権が不十分です)」 というエラーが出る。
これはビューの実行計画を見ようとしているときに、 ビュー自体のSELECT権限を持っていて、その元表になる表(のどれか) のSELECT権限を持っていない場合に発生します。 実行計画を見るには、全ての元表に対するSELECT権限も必要です。
Oracle8iでPRAGMA AUTONOMOUS_TRANSACTIONで自立型トランザクションを行わせると、 ORA-00164 「移行可能な分散トランザクション内で自立型トランザクションの処理はできません」 というエラーが出る。
トランザクションの中で、データベースリンクを使って別のデータベースを参照している場合、 自立型トランザクションは開始できません。 そのような処理を一旦確定してセッションを切り、 再度別のトランザクションとして自立型処理を含む処理を実行します。
シーケンスにいつも20くらいの飛び(欠番)が発生する!
CREATE SEQUENCE文でNOCACHEオプションをつけていないと、デフォルトで「CACHE 20」となります。 このCACHE句がついていると、1つのセッションで続けて高速に連番を発番できるように、 幾つかの値をまとめて発番してメモリに先に取得しておく、ということが行われますが、 使われずにセッションが終了すると残りは欠番になります。 20くらい飛ぶという現象は多分これが原因です。 欠番を出すのが嫌で、1セッションで1つしか発番しない場合は、NOCACHE句をつければOKです。 なおWebの情報によると、CACHEの効果はそんなに劇的なものではないそうです…
文字列の簡単な暗号化
Oracle8i以降では、DESまたはDES3方式で簡単に文字列を暗号化することができ、 ユーザーのパスワードなどをテーブルに格納する際に便利に使えます。
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
    input_string => lv_plain_pass,
    key_string => lv_key,
    encrypted_string => lv_enc_pass
);
暗号化には、「DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT」や 「DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT」を使い、 復号には「EN」が「DE」に変わった名前の関数を使います。
引数の名前を明示しているのは、 オーバーロードしている関数があるためです。 つまり引数の名前を指定しないと PLS-00307 (複数のプロシージャが宣言されている) というエラーになります。
入力文字列(上記の例のlv_plain_pass)は、文字列の長さ(バイト数)が8の倍数でなければいけません。 つまり、少なくとも8バイトの長さが必要で、 それを超える場合には16、24…という長さに文字列を合わせてやる必要があります。
元表の持ち主にビューをGRANTしてもORA-01720が発生する
スキーマAが表TABLE_Aを、スキーマBが表TABLE_Bを持っているとします。 AがBに「GRANT SELECT TABLE_A」して、 BがTABLE_AとTABLE_BからビューVIEW_ABを作りました。 普通に考えるとこのビューをそのままBがAにGRANT SELECTできそうですが、 実際にはORA-01720が発生します。
そうです、AがBに「GRANT SELECT TABLE_A」する際に、「WITH GRANT OPTION」をつけないと、 相手が元表の持ち主といえどもビューを公開することはできません。
普通に発行できるSQLをPL/SQLの中で使うとORA-00942でコンパイルエラーになる
他のユーザの表やビューにアクセスしている、 単独では普通に発行できるSQLを、PL/SQLストアドプロシージャの中で使うと 「ORA-00942:表またはビューが存在しません」になることがあります。
これは、実行に必要な権限の一部を、ロール経由で付与されていると発生します。 例えば、MY_ROLEロールをつくり、SELECT ANY TABLEシステム権限をMY_ROLEに付与し、 MY_ROLEを各ユーザに付与すれば、 各ユーザは他ユーザの表やビューを個別のGRANTなしにSELECTできるようになります。 しかし、同じSELECT文をストアドプロシージャの中で使おうとすると、 ORA-00942でコンパイルエラーになってしまうのです。
Oracleは一意制約違反になる条件がおかしい?
複数の列からなる一意制約(UNIQUE)を定義しているとき、 それらの値が全てNULLの行はいくつあってもエラーになりませんが、 一部の列の値が同じで、一部の列の値がNULLという行を複数作ろうとすると、 一意制約違反が発生します。
つまり例えば、表に3つの列からなる一意制約を付けたとします。 それらの値が(NULL,NULL,NULL)の行はいくつあっても一意制約違反になりませんが、 (値A、値B、NULL)という行が複数作られようとすると、一意制約違反になります。 直感的にはよく分からない仕様ですね。 RDBMSとはこういうものなのか、それともOracleだけが特殊なのか? 全てはなぞのままなのであった。(帰れ)
Oracle Instant Clientって
Instant Clientは 米国サイトで常に最新版が入手できるほか、 日本語サイトでも10gなら入手できます。(2008/05/27現在) 入手、設定方法は以下の通りです。
  1. お勧めは「Basic」と「SQLPlus」です。この2つをダウンロードし、 同じディレクトリで展開します。 「Basic Lite」は、日本語のNLS_LANGをサポートしておらず、 sqlplus.exeを使うと「NLS Initialization Error」というエラーになり、 PHPでも「OCIEnvNlsCreate() failed:なんとか」というエラーになり使えません。 まったく使えないと思ってよいでしょう。
  2. 特にインストーラなどでシステムに書き込んだりしないので、 展開したディレクトリは好きな場所に移動、改名できます。 ここではC:\oracle\instantclient11という名前にしたとします。
  3. 自分でテキストエディタなどを使ってtnsnames.oraを作り、 C:\oracle\instantclient11に配置します。
  4. システム環境変数を設定します。
    1.PATHにC:\oracle\instantclient11を追加
    2.TNS_ADMIN=C:\oracle\instantclient11と定義
    3.ORACLE_HOME=C:\oracle\instantclient11と定義
    4.NLS_LANGに「JAPANESE_JAPAN.JA16SJIS」など適切な値を設定
  5. これでsqlplus.exeを起動して、接続を試します。 「sqlplus taro/taropass@remote_db」問題なく接続できればokです。

  もくじ
Oracle   (C) 2002-2011 MISUMI URANO
Ads by TOK2