文書の過去の版を表示しています。
目次
Oracle
Oracle 数値の後の記号の意味
i | 8i,9iで使用された、「Internet」の先頭文字 |
---|---|
g | 10g,11gで使用された、「Grid computer」の先頭文字 |
c | 12c,18cで使用された、「Cloud」の先頭文字 |
オラクルサービスの再起動方法
コンピュータの管理→サービス
ORACLEService<SID> 開始
Oracle11g XE
Oracle Database Express Edition(以下XE)とは制限付きながら無償で商用利用できるデータベースソフトウエアです。
XEの制限
XEの制限 XEには以下の制限があります。下記サイトのように再作成することでカスタマイズできます。
Oracle XEのデータベースをSJISで作り直す
- CPUリソースは1CPU(1core)相当分まで
- SGAとPGAのメモリパラメータ値は合計1GBまで
- ユーザ表領域(SYSTEMやSYSAUXの表領域は含まない)のサイズは10g版は4GB、11g版は11GBまで
- インストール時にDBのSID(XE)とORACLE_HOMEが固定される(複数のデータベースやソフトウエアのインストールはできない)
- 文字コードは「AL32UTF8」
パッチを当てる
パッチ状態の確認
Release11.2.0.4.0 | |
---|---|
1桁目 | バージョン番号 |
2桁目 | メンテナンスリリース番号 |
3桁目 | アプリケーションサーバ製品のみ該当 |
4桁目 | コンポーネント固有のリリース番号 |
5桁目 | プラットフォーム固有のリリース番号 |
Oracleのバージョンの確認では、5桁目が「0」で表示されてしまう仕様である。
SELECT * FROM v$version;
5桁目のバージョンを確認するには、OPatchで確認する。
「D:\oracle\product\11.2.0\db_1\OPatch」フォルダにて下記をコマンドを実行すると表示される。
opatch lsinventory
パッチがあたっていると下記のようにでる。
「21821802;WINDOWS DB BUNDLE PATCH 11.2.0.4.20:(21821802)」
パッチがあたってないと
「このOracleホームには仮パッチがインストールされていません。」
パッチの適用
11.2.0.4.20のパッチを解凍して、Oracleフォルダ内にPatchフォルダとしてコピーする。
;p21821802_112040_MSWIN-x86-64は、11.2.0.4.20のパッチです。 Oracle 11g Patchset\p21821802_112040_MSWIN-x86-64.zip
Opatchのバージョンが古いと駄目なので最新にする。解凍してOracleフォルダ内Opatchに上書きコピーする。
;p6880880_112000_MSWIN-x86-64は、オーパッチ Ver 11.2.0.3.15です。 Opatch 11g\p6880880_112000_MSWIN-x86-64.zip
Oracleフォルダ内にPatchフォルダのパッチ番号(例 21821802)フォルダにカレントフォルダを移動して、下記コマンドを入力する。
..\..\OPatch apply
先にOracleの必要なサービスを停止した後、電子メールの登録等は無視して実行する。
パッチ適用後にバージョン「opatch lsinventory」を確認し、無難にPCを再起動する。
Oracle11gR2のpatch applyで詰まった件(mstdcが原因だった)
Oracleにて「~(チルダ)」の文字が化ける現象について(WaveDash問題)
OracleでDBに「~(チルダ)」を格納し、Select文で取得すると「?」に文字化ける割と有名な問題点
Oracleをインストールするときの設定漏れに注意。
環境変数をNLS_LANG = JAPANESE_JAPAN.JA16SJISTILDEにしておく。
ODBC, oo4oなど内部で一旦Unicodeに変換するミドルウェアにおいて、「~」という文字に対するSJISの波ダッシュ(0x8160)とUnicode波ダッシュ(U+301C:Wave dash)のマッピング(全角チルダ(U+FF5E:Fullwidth Tilde)に割り当てている)がOracleとMicrosoftの間で異なっているのが原因です。
半角の丸括弧「)」がパス(フォルダ、ファイル名)に含まれてるとOracle接続が出来ない
SQL*NetやNet8では、コネクトの初期処理で、Program名などの情報をOracleに渡します。 この事は、sqlnet.log を確認すると分かるかと思いますが、接続文字列が ...(CID=(PROGRAM=C:\test\(test)\Project1.exe)... などと記録される事になります。
Oracleのパースの仕方に問題があるんですが、しょうがないですよね。
開発上フォルダ名を「機能ID(機能名)」としていたため、フォルダの括弧は外すように対応しました。
半角スペース有りの文字列比較
以下の2つのセマンティクスがあること
- 非空白埋め比較セマンティクス
比較する文字列のどちらかが(もしくはどちらも)VARCHAR2の場合 - 空白埋め比較セマンティクス
比較する文字列のどちらもCHAR(またはリテラル)の場合
1の場合、半角スペースは見た目そのままの扱いで検索されます。
2の場合、半角スペースは無視されたように検索されます。
CHAR と VARCHAR2 の違い ~ 文字列比較セマンティクス ~
※SQLServerではWHERE条件時のvarchar型の末尾空白は無視される。WHERE条件時に末尾空白を無視
副問い合わせ(EXISTS)を使った更新(UPDATE)の注意
売上取り消し処理が遅いということで調査していたのですが、構文をみても単純である為、なかなか気がつきませんでした。
結局遅い原因として、副問い合わせ(EXISTS)を使った更新(UPDATE)において、EXISTSの中に結合条件以外に外側の条件を含めていたため、処理に時間がかかっていたようです。
これにより、別の処理含めても1分→5秒になりました。作成時は件数が少なかったので気が付かなかったのかも知れませんが、反省を含めて掲載しておきます。
ちなみに私が作成したわけではないですが、すぐに気が付かなかったという点では知識不足だったわけです。
UPDATE T_JUCHU_H JH SET KANRYO_KBN = '0' -- 完了区分 0:未完了 1:完了 WHERE EXISTS ( SELECT 1 FROM T_URIAGE_M UM WHERE UM.JUCHU_NO = JH.JUCHU_NO AND UM.JOTAI_KBN = '0' -- AND JH.JOTAI_KBN = '0'; ← EXISTS の中にあると遅い ) AND JH.JOTAI_KBN = '0'; ← EXISTS の外にすることで速くなった
インデックスは、JH.JUCHU_NOのみ、UM.JUCHU_NO と JOTAI_KBN はありません。
実行計画
consistent gets:SELECT(FOR UPDATE句有り)文 実行時のデータ要求
これ以外の項目はほぼ同じ値
EXISTS内: consistent gets 11657696
EXISTS外: consistent gets 1070
PL/SQL(ストアド)をVisual Studio上でデバッグする
Oracleの開発するときに使用する某社製品(2009年当時)で、PL/SQL(ストアド)をデバッグしようとすると、処理が固まって動作しないことがあります。
Visual Studioでデバッグする方法の記事を見たことがあったなーと記憶の片隅にありました、ってことで早速、実践。便利、便利、って最初からこうすれば良かった。
ODT 10.2までは、IDEに「Oracleエクスプローラ」という独自のツール・ウィンドウですが、ODT 11.1からはVisual StudioのIDEのサーバ・エクスプローラに統合されています。
もし、下記のようなエラーが発生した場合
- エラー内容
Oracle Developer Tools for Visual Studio .NET({D601BB95-E404-4A8E-9F24-5C1A462426CE}) は、以前のエラーが原因で読み込むことができませんでした。 詳細についてはパッケージ開発元に問い合わせてください。 このパッケージをもう一度読み込むには、コマンド プロンプトで 'devenv /resetskippkgs' と入力してください。
GAC に登録されている Oracle.DataAccess.dll のバージョンが古い場合に、この現象が発生するそうです。
GAC にOracle.DataAccess.dll のバージョンを登録すると直ります。
(1) C:\WINDOWS\Assembly をエクスプローラで開きます。
(2) Oracle.DataAccess ファイルをエクスプローラで、C:\WINDOWS\Assembly にドラッグ&ドロップする。
(3) コマンド プロンプトで 'devenv /resetskippkgs' と入力してください。
その他
ORACLE DBLINKによるSQLSERVER接続
作成するプログラムで、SQLServerにある某マスタデータを参照してOracle上の某マスタに登録・更新する機能があり、仕様設計者からOracle上からSQLServerのデータを参照することが出来るのか調査して欲しいとの依頼があり、DBLinkを使えば出来そうだと参考サイトを印刷して渡しました。
DBLINKでSQLSERVER接続
数日後、印刷したサイトなどを参考にしたが、「ORA-12541: TNS: リスナーがありません。」とエラーが出て接続できなかったとのことで、私が引き継いで調査することになりました。
私もこれにはまりましたね、数時間格闘した結果、ふとひらめいたんです。
tnsnames.oraで、HOST名を指定しているところに、別端末のSQLServerのIPアドレスが書かれていました。
SQLServerに接続するんだから、これでいいと思い込んでたんですね(T-T)
でも、ODBC接続でSQLServerに接続しているのだから、このHOST名はOracleのままにしておけばいいのではないかと、まさに正解でした(^-^)
やっとこさ、リスナーエラーから解放されて、接続できるかと思いきや次のエラーが発生しました(^-^;;
「ORA-28500:NT AUTHORITY\ANONYMOUS LOGINはログインできませんでした。」
ODBC接続にて、SQL ServerへはWindows認証で設定されていたので、ユーザーを作成すればいいかもと、SQL Serverでユーザーを作成して、SQLServer認証に切り替えたところ、DBLinkでSQL Serverに接続することが出来ました。
接続出来たことを報告すると、ユーザーを作成しないで出来ないかとのことで再調査しました。
SI Object Browser上でDBLinkを作成するとユーザー名の「SA」と英大文字に自動変換されてしまう。
これが接続できない理由ではないかと思い、そこでソースにて、“”(2重引用符)で囲むことで英小文字“sa”のままとなるとの情報をみつけ、ユーザー名とパスワードをそれぞれ““で囲んでみたところ、接続することが出来ました。(= ̄▽ ̄=)V やったね!
参照
Date型のバインド変数の指定方法
SI Object BrowserやVisualStudio等で、ストアドのデバッグをする際にバインド変数がDate型のものに対して、日時の引渡し方法が分からなかった為、(日のみの指定なら渡せるが、日時だと渡せない)
わんくま掲示板に質問し、解決することが出来ました。
今回、過去ログに移動したため、Blogの記事として転記します。
Oracle Date型のバインド変数の指定方法
魔界の仮面弁士 さんの下記の回答により解決しました。
select * from v$nls_parameters を実行して、NLS_DATE_FORMAT の値を確認してみてください。 もし、それが時刻部を含めた表記になっていないのであれば、 システム環境変数 NLS_DATE_FORMAT に「YYYY-MM-DD HH24:MI:SS」をセットしてから Visual Studio を起動し、[Oracle エクスプローラ]からストアドを実行してみてください。
現状、NLS\_DATE\_FORMAT の値が「RR-MM-DD」となっていた。
これにより、日のみの指定しか出来なかったわけですね。
開発途中段階で、システム環境変数を変更するわけにもいかないため、自分の環境化だけ下記サイトを参考に変更しました。
レジストリエディタにて
[HKEY_LOCAL_MACHINE]-[SOFTWARE]-[ORACLE]-[HOME0]の下に、[NLS\_DATE\_FORMAT]を追加して、値を「yyyy/mm/dd hh24:mi:ss」に設定する。
これにより、「Visual Studio」と「SI Object Browser」で日時パラメータが認識されるようになりました。
カーソルを引数で渡す
ストアドでそれなりの処理を作成しているときに、カーソルを引数として共通化したい場合がある。
単一テーブルのカーソルの引数の受け渡しなら下記サイトを参考に出来たんだけども
カーソル変数(REF CURSOR)の使用
売上テーブルと仕入テーブルなど複数テーブルからカーソルの引数の受け渡しをして、共通のテーブルにデータを更新したいといった場合、どうしたものかと考えて、下記方法で対処した。
もっといい方法があるかも知れないけど、とりあえず備忘録として記述
※共通処理側で値チェックや計算処理などすることを想定
CREATE OR REPLACE PACKAGE HOGE IS TYPE gencurtyp IS REF CURSOR; PROCEDURE TEST; PROCEDURE CUR_TEST( gencurtyp_cv IN OUT gencurtyp ); END; / CREATE OR REPLACE PACKAGE BODY HOGE IS PROCEDURE TEST IS generic_cv gencurtyp; BEGIN -- HOGEテーブルの定義に合わせる OPEN generic_cv FOR SELECT '1234' AS CODE ,20090620 AS YMD FROM DUAL; -- HOGEテーブルに挿入 CUR_TEST(generic_cv); END TEST; -- カーソル引数により、HOGEテーブルに挿入 PROCEDURE CUR_TEST( gencurtyp_cv IN OUT gencurtyp -- テーブル名 ) IS gencurtyp_rec HOGE%ROWTYPE; BEGIN FETCH gencurtyp_cv INTO gencurtyp_rec; INSERT INTO HOGE( CODE , YMD ) VALUES( gencurtyp_rec.CODE , gencurtyp_rec.YMD ); END CUR_TEST; END HOGE;
トリガー処理
プロジェクト終盤になって、幾つかのマスタ系およびトランザクション系の更新ログを取りたいというユーザーから要望を受ける。
十数本ある機能を今更修正するには時間もコストも無いってことで、検討した結果、トリガー処理を思いつく。
今までトリガーの存在こそ知っていても使う機会が残念ながら無かった、今回ちょうどいい勉強の機会であった。
ちなみに、トリガーとは表に対して何らかの変更処理(挿入、更新、削除)が加えられたとき、その変更処理をきっかけとして自動的に実行される特殊なストアドプロシージャのことです。
14. トリガー (SQL 非標準)
各ログには、画面からの処理モード(登録・更新・削除)を項目にセットする必要があるのだが、トリガー処理では、外部から引数を受け渡すことが出来ない、そこでログ用の一時表を作成し、処理モードをセットしてトリガー内で値を受け取るようにした。
画面からのテーブル更新でなければ、一時表には何も書かれてないため直接DBを書き換えたと判断できる。
備忘録として記述
- Sample
CREATE OR REPLACE TRIGGER M_HOGE_TRI BEFORE INSERT OR DELETE OR UPDATE ON M_HOGE REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE MODE_KBN VARCHAR2(1); -- I:挿入、U:更新、D:削除をセット MODE_NAME VARCHAR2(4); -- "新規"、"修正"、"削除"をセット BEGIN BEGIN -- 画面から更新の場合は、一時表に値がセットされるが、 -- DBから直接の場合は、一時表に値が無い状態となる。 -- 一時表であるため、値取得不可の場合がある。 SELECT MODE_SHORI INTO MODE_NAME FROM W_LOG; EXCEPTION WHEN NO_DATA_FOUND THEN MODE_NAME := '直DB'; -- 直接DBで入力 END; IF INSERTING THEN MODE_KBN := 'I'; -- I:挿入 ELSIF UPDATING THEN MODE_KBN := 'U'; -- U:更新 ELSIF DELETING THEN MODE_KBN := 'D'; -- D:削除 END IF; IF INSERTING OR UPDATING THEN -- 挿入 or 更新 内容をログに吐き出し INSERT INTO L_HOGE VALUES( L_HOGE_SEQ.NEXTVAL , MODE_KBN , MODE_NAME , SYSDATE , :NEW.CODE , :NEW.NAME ); ELSE -- 削除 内容をログに吐き出し INSERT INTO L_HANYO VALUES( L_HOGE_SEQ.NEXTVAL , MODE_KBN , MODE_NAME , SYSDATE , :OLD.CODE , :OLD.NAME ); END IF; END M_HOGE_TRI;
トラブル対応
ORA-00257
原因
アーカイブログの出力先のディスク容量が足りない。
対応
下記のコマンドを実行する。
- cmd
rman target sys/Manager01@orabompj RMAN> DELETE ARCHIVELOG ALL; //その他 指定日付まで削除する場合 RMAN> DELETE ARCHIVELOG UNTIL TIME= "TO_DATE('2016-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS')"; RMAN> DELETE ARCHIVELOG UNTIL TIME 'sysdate';
先にOSからファイルを削除してしまった場合
上記のコマンドでは領域が開放されないので下記を実行する。
- cmd
rman target sys/Manager01@orabompj RMAN> CROSSCHECK ARCHIVELOG ALL; RMAN> DELETE EXPIRED ARCHIVELOG ALL;
その他
バッチを作成する場合の注意
Windowsサービスはコンピュータ名に”-“が含まれていると、Oracle側で自動削除されるため、”-“を除去する
例 コンピュータ名 HOGE-FUGA
Oracleサービス OracleServoceHOGEFUGA
フォルダのコンピューター名はそのまま、”-“除去は不要
D:\oracle\diag\tnslsnr\HOGE-FUGA\listener\trace\listener.log
- cmd
rem コンピュータ名から"-"除去 set COMPUTERNAME2=%COMPUTERNAME:-=%
Tips
複数列副問合せ
WHERE (DEPTNOとSAL)IN(SELECT DEPTNO,MAX(SAL) などのように、括弧で囲んで複数列指定する
http://itpro.nikkeibp.co.jp/article/COLUMN/20070914/281985/
日付型関連
部材受入れシステムの最終更新日時(DATE型に変換)を取得する。
- 最終更新日時の取得
SELECT MAX(TO_DATE(EC || ' ' || ED, 'YYYYMMDD HH24MISS')) AS LATESTDATE FROM RESH003040
部材受入れシステムの更新日時(DATE型から文字列変換)で抽出する。
- 更新日時で抽出
SELECT * FROM RESH003040 WHERE EC || ED >= TO_CHAR(TIMESTAMP '2017-02-08 15:40:15', 'YYYYMMDDHH24MISS')
参照
半角スペース有りの文字列比較
以下の2つのセマンティクスがあること
- 非空白埋め比較セマンティクス
比較する文字列のどちらかが(もしくはどちらも)VARCHAR2の場合 - 空白埋め比較セマンティクス
比較する文字列のどちらもCHAR(またはリテラル)の場合
1の場合、半角スペースは見た目そのままの扱いで検索されます。
2の場合、半角スペースは無視されたように検索されます。
CHAR と VARCHAR2 の違い ~ 文字列比較セマンティクス ~
※SQLServerではWHERE条件時のvarchar型の末尾空白は無視される。WHERE条件時に末尾空白を無視
1レコード前の日時差を求める
阿南部品からロス重量実績システムで2回同じのを登録してしまったとの削除依頼の問合せがくる。
よって、グループ単位の1レコード前の差が10分以内のを抽出するSQLを作成した。
グループ単位に、検索したレコードのn行前のデータを取得する( LAG )
- 1コード前日時差取得
SELECT B.*, TRUNC((WORK_DATE - PRE_WORK_DATE) * 24*60*60) AS DIFF FROM( SELECT A.*, LAG(WORK_DATE) OVER(partition BY KOUTEI_CD,WORKER_CD,BOX_CD,WASTE_G,TOTALL_G,TO_CHAR(WORK_DATE,'YYYYMMDD') ORDER BY KOUTEI_CD,WORKER_CD,BOX_CD,WASTE_G,TOTALL_G,CNT) AS PRE_WORK_DATE FROM HJ_WASTE A WHERE TRUNC(A.WORK_DATE) >= TO_DATE('2012/01/01', 'YYYY/MM/DD') ) B WHERE CNT > 1 AND TRUNC((WORK_DATE - PRE_WORK_DATE) * 24*60*60) <= 60*10 -- 秒単位なので60秒x10=600秒=10分 ORDER BY WORK_DATE
アカウントロックした場合の対処
alter user ロックしたアカウントのユーザ名 account unlock;
全てのテーブル件数の取得
Analyze後の件数なので正確ではない、大まかな件数の把握向け。
SELECT T.TABLE_NAME, T.NUM_ROWS , C.COMMENTS FROM USER_TABLES T, USER_TAB_COMMENTS C WHERE C.TABLE_NAME(+)=T.TABLE_NAME
Oracle11g XE
Oracle Database Express Edition(以下XE)とは制限付きながら無償で商用利用できるデータベースソフトウエアです。
Oracle11g XE ダウンロード
XEの制限
XEの制限 XEには以下の制限があります。下記サイトのように再作成することでカスタマイズできます。
Oracle XEのデータベースをSJISで作り直す
- CPUリソースは1CPU(1core)相当分まで
- SGAとPGAのメモリパラメータ値は合計1GBまで
- ユーザ表領域(SYSTEMやSYSAUXの表領域は含まない)のサイズは10g版は4GB、11g版は11GBまで
- インストール時にDBのSID(XE)とORACLE_HOMEが固定される(複数のデータベースやソフトウエアのインストールはできない)
- 文字コードは「AL32UTF8」
※WH_KOUSEIの文字コードは「AL32UTF8」なので変更不要
ODP.NET Managed Driver
「ODP.NET Managed Driver」なら、クライアントPCにOracleクライアントのインストールが不要となる。
VisualStudioの場合にはNugetマネージャを起動して検索ウィンドウに「ODP.NET Managed Driver」と入力して参照を追加すれば使用できます。名前空間は「Oracle.ManagedDataAccess.*」となります。
Oracle12cについて
・ユーザパスワードが大文字・小文字を識別設定について
元々のパスワードが大文字・小文字を複合しているパスワードの場合、大文字・小文字の識別設定を「False」にすると正しく入力しても違うパスワードと判定してしまう。
プラカブルデータベース(PDB)の追加制限について
PDBに関してはStandard Editionで作成を行うと1つしか作成できない。 作成制限については下記URLを参照 https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1197781_2274.html https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1197776_2274.html
Enterprise Managerについて
Oracle9i,10gで使用されていた「Enterprise Manager」は12cで廃止され、「Oracle Enterprise Manager Database Express」通称「EM Express」に変更されました。
「Enterprise Manager」に接続するために「https://localhost:1158/em/」にアクセスしていましたが「EM Express」では「https://localhost:5500/em/」でアクセスするようになります。
※Flashのインストールが必須(表示に必要)
クライアントバージョンのサポート状況
Oracle Client / Server Interoperability Support
Oracle12cで、サポート外のoracle.dataaccess.dll 2.102.4.0を使用すると、「Oracle.DataAccess .Client.OracleConnection' のタイプ初期化子が例外をスローしました。」エラーとなる。
サーバーバージョン | |||||
---|---|---|---|---|---|
クライアント | 12.2.0#10 | 12.1.0 | 11.2.0 | 11.1.0 | 10.2.0 |
12.2.0 | Yes | Yes | Yes | No | No |
12.1.0 | Yes | Yes | Yes | Was | MDS #7 |
11.2.0 | Yes #9 | Yes | Yes | Was | MDS #7 |
11.1.0 | No | Was | Was | Was | Was #7 |
10.2.0 | No | MDS #7 | MDS #7 | Was #7 | MDS |
Key | |
---|---|
Yes | サポートされます。 |
ES | サポートされます。但し、Extended Support契約を有する場合のみ不具合の修正が可能になります。 |
LES または MDS | サポートされます。但し、有効な Limited Extended Support または Market-Driven Support 契約を有する場合のみ不具合の修正が可能になります。 |
Was | 以前、この組み合わせはサポートされていましたが、現在は、このリリースのいずれもPremier Support、Primary Error Correct Support、Extended Support、Extended Maintenance Supportでカバーされていないため、不具合の修正は行われません。 |
NO | サポートされていません。 |
注釈 | |
---|---|
#7 | バージョン11.1以上とバージョン10.1/10.2との間で接続する場合、これらのバージョン間で、PLSSQLを使用するためにはそれぞれ、10.2.0.2 以上を使用しなければなりません。 |
#9 | 11.2.0.3 または 11.2.0.4 のみ。Oracle Database Exadata Express Cloud Service と 11.2 クライアントの互換性はサポートしていません。 |
#10 | Oracle Database Exadata Express Cloud Service を含みます。 |