====== Oracle ====== ===== Oracle 数値の後の記号の意味 ===== ^i|8i,9iで使用された、「Internet」の先頭文字| ^g|10g,11gで使用された、「Grid computer」の先頭文字| ^c|12c,18cで使用された、「Cloud」の先頭文字| ===== オラクルサービスの再起動方法 ===== コンピュータの管理→サービス\\ ORACLEService 開始\\ ===== Oracle11g XE ===== Oracle Database Express Edition(以下XE)とは制限付きながら無償で商用利用できるデータベースソフトウエアです。\\ * [[http://www.oracle.com/technetwork/jp/database/database-technologies/express-edition/overview/index.html|Oracle11g XE ダウンロード]] * [[https://news.mynavi.jp/article/20110903-a022/|無償Oracleデータベース商用利用]] * [[http://d.hatena.ne.jp/replication/20121119/1353300326|Oracle 11g XEでユーザ登録からテーブル作成までをやってみた。]] * [[http://d.hatena.ne.jp/replication/20121119/1353304150|Oracle 11g XEにおいて一般ユーザでcreate tableする方法]] * [[http://d.hatena.ne.jp/wutse/20070924/1190644224|Oracle XEでテーブルスペース作成]] ==== XEの制限 ===== XEの制限 XEには以下の制限があります。下記サイトのように再作成することでカスタマイズできます。\\ [[http://www.ujp.jp/modules/tech_regist2/index.php?Oracle%2F11g%2Fexpress%2F05createdb.batSJIS&offset=0|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^^ |１桁目|バージョン番号| |２桁目|メンテナンスリリース番号| |３桁目|アプリケーションサーバ製品のみ該当| |４桁目|コンポーネント固有のリリース番号| |５桁目|プラットフォーム固有のリリース番号| 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を再起動する。\\ [[http://suzumata.blogspot.jp/2011/10/oracle11gr2patch-applymstdc.html|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の間で異なっているのが原因です。\\ [[http://www.shift-the-oracle.com/guidlines.html|アプリケーション設計と開発時のガイドライン]] ===== 半角の丸括弧「)」がパス(フォルダ、ファイル名)に含まれてるとOracle接続が出来ない ===== SQL*NetやNet8では、コネクトの初期処理で、Program名などの情報をOracleに渡します。 この事は、sqlnet.log を確認すると分かるかと思いますが、接続文字列が ...(CID=(PROGRAM=C:\test\(test)\Project1.exe)... などと記録される事になります。 Oracleのパースの仕方に問題があるんですが、しょうがないですよね。\\ 開発上フォルダ名を「機能ID(機能名)」としていたため、フォルダの括弧は外すように対応しました。 ==== 半角スペース有りの文字列比較 ==== [[http://blog.goo.ne.jp/toritori0318/e/7f42f788cd2bc10df1e91b9cab463c65|Oracle小話その９　CHARとVARCHAR2の違い]] 以下の２つのセマンティクスがあること\\ - 非空白埋め比較セマンティクス \\ 比較する文字列のどちらかが（もしくはどちらも）VARCHAR2の場合 - 空白埋め比較セマンティクス \\ 比較する文字列のどちらもCHAR（またはリテラル）の場合 1の場合、半角スペースは見た目そのままの扱いで検索されます。\\ 2の場合、半角スペースは無視されたように検索されます。\\ [[http://www.shift-the-oracle.com/element/data-type/varchar2-char-comparison.html|CHAR と VARCHAR2 の違い ～ 文字列比較セマンティクス ～]] ※SQLServerではWHERE条件時のvarchar型の末尾空白は無視される。[[IT技術:データベース:sqlserver#WHERE条件時に末尾空白を無視|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でデバッグする方法の記事を見たことがあったなーと記憶の片隅にありました、ってことで早速、実践。便利、便利、って最初からこうすれば良かった。 * [[http://www.atmarkit.co.jp/ait/articles/0705/07/news083_2.html|ODTを使ったVisual StudioからのPL/SQL開発]] * [[http://otn.oracle.co.jp/obe/net11gobe/debugging/debugging.htm|Visual StudioからのOracle PL/SQLのデバッグ]] * [[http://www.atmarkit.co.jp/fdotnet/vblab/oracledbentry_01/oracledbentry_01_03.html|Oracleデータベース開発のテクノロジ]] 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' と入力してください。 * [[http://otn.oracle.co.jp/forum/thread.jspa?threadID=8010924&start=0&tstart=0|Oracleエクスプローラが表示できない(ODP) ]] * [[http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=48158&forum=26|VisualStudio上でOracleエクスプローラが表示されない]] GAC に登録されている Oracle.DataAccess.dll のバージョンが古い場合に、この現象が発生するそうです。\\ GAC にOracle.DataAccess.dll のバージョンを登録すると直ります。\\ (1) C:\WINDOWS\Assembly をエクスプローラで開きます。\\ (2) Oracle.DataAccess ファイルをエクスプローラで、C:\WINDOWS\Assembly にドラッグ&ドロップする。\\ (3) コマンド プロンプトで 'devenv /resetskippkgs' と入力してください。 ==== その他 ==== * [[http://kagamihoge.hatenablog.com/entry/20111110/1320926046|OracleのSQL Developerが便利 デバッグ実行]] * [[https://codezine.jp/article/detail/1491|ASP.NETからPL/SQLをデバッグする]] * [[http://www.atmarkit.co.jp/ait/articles/0707/05/news125_3.html|IIS上でのPL/SQLデバッグ]] ===== ORACLE DBLINKによるSQLSERVER接続 ===== 作成するプログラムで、SQLServerにある某マスタデータを参照してOracle上の某マスタに登録・更新する機能があり、仕様設計者からOracle上からSQLServerのデータを参照することが出来るのか調査して欲しいとの依頼があり、DBLinkを使えば出来そうだと参考サイトを印刷して渡しました。\\ [[http://web.archive.org/web/20090717021820/http://sqlmemo.seesaa.net/article/12208628.html|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」と英大文字に自動変換されてしまう。\\ これが接続できない理由ではないかと思い、そこでソースにて、""(２重引用符)で囲むことで英小文字"sa"のままとなるとの情報をみつけ、ユーザー名とパスワードをそれぞれ""で囲んでみたところ、接続することが出来ました。(=￣▽￣=)V やったね！ ==== 参照 ==== * [[https://www.nekojab.com/dblink_oralce_sqlserver.html|OracleからSQLServerへのDB-LINK(データベースリンク)の作成方法]] * [[http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19223-01/gencon.htm|Generic Connectivity]] * [[http://www.atmarkit.co.jp/ait/articles/0507/08/news117_3.html|Excel‐Oracle連携　Oracle Generic Connectivityを使用する方法]] ===== Date型のバインド変数の指定方法 ===== SI Object BrowserやVisualStudio等で、ストアドのデバッグをする際にバインド変数がDate型のものに対して、日時の引渡し方法が分からなかった為、(日のみの指定なら渡せるが、日時だと渡せない)\\ わんくま掲示板に質問し、解決することが出来ました。\\ 今回、過去ログに移動したため、Blogの記事として転記します。\\ [[http://bbs.wankuma.com/index.cgi?mode=al2&namber=35823&KLOG=62|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」で日時パラメータが認識されるようになりました。 ===== カーソルを引数で渡す ===== ストアドでそれなりの処理を作成しているときに、カーソルを引数として共通化したい場合がある。\\ 単一テーブルのカーソルの引数の受け渡しなら下記サイトを参考に出来たんだけども\\ [[http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19257-01/sqloperations.html#9160|カーソル変数（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; ===== トリガー処理 ===== プロジェクト終盤になって、幾つかのマスタ系およびトランザクション系の更新ログを取りたいというユーザーから要望を受ける。\\ 十数本ある機能を今更修正するには時間もコストも無いってことで、検討した結果、トリガー処理を思いつく。\\ 今までトリガーの存在こそ知っていても使う機会が残念ながら無かった、今回ちょうどいい勉強の機会であった。\\ ちなみに、トリガーとは表に対して何らかの変更処理(挿入、更新、削除)が加えられたとき、その変更処理をきっかけとして自動的に実行される特殊なストアドプロシージャのことです。\\ [[http://www.techscore.com/tech/sql/SQL14/14_01.html|14. トリガー (SQL 非標準)]] 各ログには、画面からの処理モード(登録・更新・削除)を項目にセットする必要があるのだが、トリガー処理では、外部から引数を受け渡すことが出来ない、そこでログ用の一時表を作成し、処理モードをセットしてトリガー内で値を受け取るようにした。\\ 画面からのテーブル更新でなければ、一時表には何も書かれてないため直接DBを書き換えたと判断できる。\\ 備忘録として記述\\ 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 -- 画面から更新の場合は、一時表に値がセットされるが、 -- ＤＢから直接の場合は、一時表に値が無い状態となる。 -- 一時表であるため、値取得不可の場合がある。 SELECT MODE_SHORI INTO MODE_NAME FROM W_LOG; EXCEPTION WHEN NO_DATA_FOUND THEN MODE_NAME := '直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; ===== トラブル対応 ===== ==== 数秒で終わるはずのSQLがなぜか永遠に終わらない場合 ==== [[http://webmaid.blog.jp/archives/70282923.html|Oracleで、数秒で終わるはずのSQLがなぜか永遠に終わらない場合の対処方法]] === 原因 === この現象の原因は、OracleのSHARED POOL（共有プール）にキャッシュされたSQL実行計画です。 SQLがなぜか流れない、SQL文の"SELECT"や"WHERE"を1文字だけ変えてみて実行できるようになった場合は、ほぼこれが原因と考えてよいと思います。 === 対応 === キャッシュされたSQL実行計画を削除してあげればOKです。\\ Oracleの管理者権限で「ALTER SYSTEM FLUSH SHARED_POOL;」というSQLを実行してあげると、キャッシュされているSQL実行計画がすべて削除されます。実行後にOracleの再起動などは不要です。 また、上記の構文を流さなくても、Oracleを再起動すればSHARED_POOLもクリアされます。 ※ただし、問題のSQLだけでなく、その他のSQLの実行計画も削除されてしまうので、他の処理のレスポンスも最初の1回はわずかに速度下がってしまうこともある。 ==== ORA-00257 ==== === 原因 === アーカイブログの出力先のディスク容量が足りない。 === 対応 === 下記のコマンドを実行する。 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からファイルを削除してしまった場合 === 上記のコマンドでは領域が開放されないので下記を実行する。 rman target sys/Manager01@orabompj RMAN> CROSSCHECK ARCHIVELOG ALL; RMAN> DELETE EXPIRED ARCHIVELOG ALL; 参考:[[http://qiita.com/yjnk/items/83312094d343a1c57f25|OracleでORA-00257が出た時のリカバリ]] ==== ORA-12504 ==== ORA-12504: TNS: リスナーはCONNECT_DATAのSERVICE_NAMEを取得できませんでした。 === 原因 === DataSourceに指定する内容が間違っており、ホスト名のIPアドレスを指定しまっていた。 === 対応 === DataSourceにTNSネーム(XE)を指定した。 ==== System.Data.OracleClientの例外エラー ==== System.Data.OracleClient には Oracle クライアント ソフトウェア バージョン 8.1.7 以降が必要です。 === 原因 === ASP.NETからOracleにアクセスする権限がない。 === 対応 === Authenticated Usersグループ権限をOracleホーム・ディレクトリ(D:\Oracle\product\11.2.0)に付与する、その後IISResetする。\\ https://codeday.me/jp/qa/20181218/76959.html ==== Oracle.DataAccess.Clientの例外エラー ==== Oracle.DataAccess.Client.OracleConnection' のタイプ初期化子が例外をスローしました。\\ Oracle.DataAccess.Client.OracleException プロバイダは、このバージョンのOracleクライアントと互換性がありません === 原因 === Oracle11Client(32bit)とOracle10Client(32bit)を混在してインストールしていた。 === 対応 === Oracle10Client(32bit)をアンインストールし、Oracle11Client(32bit)のみとした。 ===== その他 ===== ==== バッチを作成する場合の注意 ==== Windowsサービスはコンピュータ名に"-"が含まれていると、Oracle側で自動削除されるため、"-"を除去する\\ 例 コンピュータ名 HOGE-FUGA\\ Oracleサービス　OracleServoceHOGEFUGA フォルダのコンピューター名はそのまま、"-"除去は不要\\ D:\oracle\diag\tnslsnr\HOGE-FUGA\listener\trace\listener.log rem コンピュータ名から"-"除去 set COMPUTERNAME2=%COMPUTERNAME:-=% ===== Tips ===== ==== 複数列副問合せ ==== WHERE (DEPTNOとSAL）IN(SELECT DEPTNO,MAX(SAL) などのように、括弧で囲んで複数列指定する\\ http://itpro.nikkeibp.co.jp/article/COLUMN/20070914/281985/\\ [[https://qiita.com/lithtle/items/4517785c67843f601443|備忘録:SQLのINを複数列指定で使う]] ==== 日付型関連 ==== 最終更新日時(DATE型に変換)を取得する。 SELECT MAX(TO_DATE(EC || ' ' || ED, 'YYYYMMDD HH24MISS')) AS LATESTDATE FROM TEST 部材受入れシステムの更新日時(DATE型から文字列変換)で抽出する。 SELECT * FROM TEST WHERE EC || ED >= TO_CHAR(TIMESTAMP '2017-02-08 15:40:15', 'YYYYMMDDHH24MISS') 参照 * [[http://www.shift-the-oracle.com/sql/functions/to_char-datetime.html|日時式、日付型をフォーマットする、曜日、和暦を求める]] * [[http://www.shift-the-oracle.com/sql/functions/trunc-datetime.html|秒単位を切り捨てる、週の初めの日付を求める、四半期を求める、年を･･･]] ==== 半角スペース有りの文字列比較 ==== [[http://blog.goo.ne.jp/toritori0318/e/7f42f788cd2bc10df1e91b9cab463c65|Oracle小話その９　CHARとVARCHAR2の違い]] 以下の２つのセマンティクスがあること\\ - 非空白埋め比較セマンティクス \\ 比較する文字列のどちらかが（もしくはどちらも）VARCHAR2の場合 - 空白埋め比較セマンティクス \\ 比較する文字列のどちらもCHAR（またはリテラル）の場合 1の場合、半角スペースは見た目そのままの扱いで検索されます。\\ 2の場合、半角スペースは無視されたように検索されます。\\ [[http://www.shift-the-oracle.com/element/data-type/varchar2-char-comparison.html|CHAR と VARCHAR2 の違い ～ 文字列比較セマンティクス ～]] ※SQLServerではWHERE条件時のvarchar型の末尾空白は無視される。[[開発:データベース:sqlserver#WHERE条件時に末尾空白を無視|WHERE条件時に末尾空白を無視]] ==== 1レコード前の日時差を求める ==== 阿南部品からロス重量実績システムで2回同じのを登録してしまったとの削除依頼の問合せがくる。\\ よって、グループ単位の1レコード前の差が10分以内のを抽出するSQLを作成した。\\ [[http://oracle.se-free.com/dml/0601_lag.html|グループ単位に、検索したレコードのn行前のデータを取得する（ LAG ）]] 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;'' ==== 全てのテーブル件数の取得 ==== [[http://tunes.sakura.ne.jp/tipi/?Oracle%2F%A5%C6%A1%BC%A5%D6%A5%EB%B0%EC%CD%F7%A4%F2%B7%EF%BF%F4%A4%C8%C6%B1%BB%FE%A4%CB%C9%BD%BC%A8%A4%B9%A4%EBSQL|Oracle/テーブル一覧を件数と同時に表示するSQL]] 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 ==== 直近に実行されてSQLを取得する ==== select last_active_time,parsing_schema_name,sql_id, sql_text from v$sqlarea where parsing_schema_name <> 'SYS' and parsing_schema_name <> 'SYSMAN' and parsing_schema_name <> 'DBSNMP' and parsing_schema_name <> 'MDSYS' and parsing_schema_name <> 'EXFSYS' order by last_active_time desc しかし、SQL_TEXTは1000文字までしか保存されていないので、それ以上になる場合は上記SQLでSQL_IDがわかるのでそれを元に次のSQLを投げれる。 -- 例 sql_id:gdhax8h3wx54r SELECT sql_text FROM v$sqltext WHERE sql_id = 'gdhax8h3wx54r' ORDER BY piece しかし、分割されて行にセットされるため。\\ 結合する際に、末尾の空白が混在してしまうことに気をつけること。 * [[http://odakeiji.blog33.fc2.com/blog-entry-23.html|私が使うSQL文の捕まえ方＆実行計画取得方法(前編)]] * [[http://odakeiji.blog33.fc2.com/blog-entry-24.html|私が使うSQL文の捕まえ方＆実行計画取得方法(後編)]] * [[http://odakeiji.blog33.fc2.com/blog-entry-119.html|Oracleで実行されたSQLのバインド変数の値を知る方法]] * [[http://replication.hatenablog.com/entry/2015/06/08/090000|直近5分間に実行されたSQLを参照する]] ==== インデックスの断片率の確認 ==== 統計情報を更新しないと、index_stats にはデータが存在しない。 -- 統計情報の更新 ANALYZE INDEX インデックス名 VALIDATE STRUCTURE SELECT name AS "インデックス名" , height AS "インデックスの深さ" , lf_rows AS "リーフ行の数" , del_lf_rows AS "削除リーフ行の数" , del_lf_rows / lf_rows AS "断片率" FROM index_stats WHERE name = '(インデックス名)' * [[http://tihiro.hatenablog.com/entry/2017/08/31/120917|Oracleでインデックスの断片率を確認と再構築について。]] * [[http://fulfillment-c.com/database/index-fragmentation-stats/|索引（インデックス）の断片化の確認方法]] ==== 表領域を減らす ==== === 対応1 === 不要なユーザーを削除する。それと一時表領域を縮小するために下記を実行する。 select * from dba_temp_free_space; alter tablespace TEMP shrink space keep 500M; [[https://blog.wackwack.net/entry/2016/09/17/221810|ディスクを圧迫した一時表領域をオンラインのまま縮小する]] === 対応2 === UNDO領域を縮小する。\\ [[https://yohei-a.hatenablog.jp/entry/20090804/1249380350|肥大した UNDO 表領域を縮小する]] -- システムユーザーで接続する SQLPLUS sys/FufaHoge@TEST AS SYSDBA CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'D:\oracle\product\11.2.0\FUGA\HOGE\UNDOTBS02.DBF' SIZE 4M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1000M; alter system set undo_tablespace = UNDOTBS2; create undo tablespace UNDOTBS1 datafile 'D:\oracle\product\11.2.0\FUGA\HOGE\UNDOTBS01.DBF' size 100m reuse autoextend off; alter system set undo_tablespace = 'UNDOTBS1'; drop tablespace UNDOTBS2 including contents cascade constraints; undotbs02.dbf を削除する。 ==== 大量データの削除 ==== 大量データを削除する場合、DELETEする方法とTRUNCATEする方法またはDROP TABLEする方法がある。 === DELETE === DELETEする方法はロールバックが効くメリットがあるが、デメリットとしてORACLEのハイウォーターマーク(HWM)に変化はなく一度確保した領域は解放されない。それにより、抽出条件を指定した場合に件数は減ったが速度は変わらないということが起こる。\\ [[https://www.dbsheetclient.jp/blog/?p=2026|DELETEと領域の解放（ORACLE）]] === TRUNCATE === TRUNCATEする方法はデメリットとしてロールバックが効かない上に全件削除されるため、必要なデータを後で挿入する。メリットとして領域を解放してくれるため、件数が減ったら抽出条件を指定しても速くなる。 === DROP TABLE === テーブルを作成し直すデメリットがあるが、メリットはTRUNCATE同様になる。 ==== SQLパフォーマンス ==== [[https://www.oracle.com/technetwork/jp/ondemand/branch/120118-tuning-1503676-ja.pdf|入門！Oracleデータベース・解決パフォーマンスチューニング pdf]] ===== Oracle11g XE ===== Oracle Database Express Edition(以下XE)とは制限付きながら無償で商用利用できるデータベースソフトウエアです。\\ [[http://www.oracle.com/technetwork/jp/database/database-technologies/express-edition/overview/index.html|Oracle11g XE ダウンロード]] ==== XEの制限 ===== XEの制限 XEには以下の制限があります。下記サイトのように再作成することでカスタマイズできます。\\ [[http://www.ujp.jp/modules/tech_regist2/index.php?Oracle%2F11g%2Fexpress%2F05createdb.batSJIS&offset=0|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.*」となります。 ===== データプロバイダが見つからない ====== 64bit PCにて.NET framework 4.6を使用したアプリケーションで下記場所で「.Net Frameworkデータプロバイダが見つからない」エラーが発生する。 DbProviderFactories.GetFactory("Oracle.DataAccess.Client") OracleClientの64bit版「win64_11gR2_client.zip」をインストールしたが変わらない。\\ ODAC 64bit版「ODAC112040Xcopy_64bit.zip」をインストールする。\\ [[http://backyard.hatenablog.com/entry/20120508/p2|64bit環境へのODP.NETインストールとアプリの配布]] ダウンロードしたZipファイルを任意のディレクトリに展開し、展開したフォルダの中にあるinstall.batを実行するとインストールできます。ちなみに要管理者権限です。\\ このinstall.batは以下の4つの引数をとります。\\ [[https://blog.shibata.tech/entry/2014/10/24/074303|Xcopy版ODACを使う インストール方法]] install.bat odp.net4 [ORACLE_HOMEのパス] [ORACLE_HOME名] true ORACLE_HOME名は、レジストリ「HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\」で確認できる。\\ これで「C:\Windows\Microsoft.NET\assembly￥GAC_64\Oracle.DataAccess」フォルダが出来ればいい。\\ 下記サイトを参考に、.NET 4.0版を手動で登録する。\\ [[http://minato128.hateblo.jp/entry/2013/08/22/004746|ODP.NET 64bit を手動で GAC に登録する]] ===== 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のインストールが必須(表示に必要) ==== クライアントバージョンのサポート状況 ==== [[http://www.oracle.com/jp/system-requirement/interoperability-support-195844-ja.html|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 を含みます。|