差分

このページの2つのバージョン間の差分を表示します。

--- it技術:データベース:oracle [2017/04/06 11:34] – [半角の丸括弧「)」がパス(フォルダ、ファイル名)に含まれてるとOracle接続が出来ない] yajuadmin
+++ it技術:データベース:oracle [2020/02/04 15:20] (現在) – [大量データの削除] yajuadmin
@@ 行 3: / 行 3: @@
 ^i|8i,9iで使用された、「Internet」の先頭文字|
 ^g|10g,11gで使用された、「Grid computer」の先頭文字|
-^c|12cで使用された、「Cloud」の先頭文字|
+^c|12c,18cで使用された、「Cloud」の先頭文字|
 ===== オラクルサービスの再起動方法 =====
@@ 行 11: / 行 11: @@
 ===== Oracle11g XE =====
 Oracle Database Express Edition(以下XE)とは制限付きながら無償で商用利用できるデータベースソフトウエアです。\\
-[[http://www.oracle.com/technetwork/jp/database/database-technologies/express-edition/overview/index.html|Oracle11g 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には以下の制限があります。下記サイトのように再作成することでカスタマイズできます。\\
@@ 行 332: / 行 335: @@
 ===== トラブル対応 =====
+==== 数秒で終わるはずの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 ====
 === 原因 ===
@@ 行 357: / 行 375: @@
 参考:[[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)のみとした。
 ===== その他 =====
 ==== バッチを作成する場合の注意 ====
@@ 行 371: / 行 416: @@
 </code>
+===== Tips =====
 ==== 複数列副問合せ ====
 WHERE (DEPTNOとSAL）IN(SELECT DEPTNO,MAX(SAL) などのように、括弧で囲んで複数列指定する\\
-http://itpro.nikkeibp.co.jp/article/COLUMN/20070914/281985/
+http://itpro.nikkeibp.co.jp/article/COLUMN/20070914/281985/\\
+[[https://qiita.com/lithtle/items/4517785c67843f601443|備忘録:SQLのINを複数列指定で使う]]
+==== 日付型関連 ====
+最終更新日時(DATE型に変換)を取得する。
+<code sql  最終更新日時の取得>
+SELECT MAX(TO_DATE(EC || ' ' || ED, 'YYYYMMDD HH24MISS')) AS LATESTDATE FROM TEST
+</code>
+部材受入れシステムの更新日時(DATE型から文字列変換)で抽出する。
+<code sql 更新日時で抽出>
+SELECT * FROM TEST
+WHERE EC || ED >= TO_CHAR(TIMESTAMP '2017-02-08 15:40:15', 'YYYYMMDDHH24MISS')
+</code>
+参照
+  * [[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（またはリテラル）の場合
+の場合、半角スペースは見た目そのままの扱いで検索されます。\\
+の場合、半角スペースは無視されたように検索されます。\\
+[[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 ）]]
+<code sql 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
+</code>
+==== アカウントロックした場合の対処 ====
+'' 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後の件数なので正確ではない、大まかな件数の把握向け。
+<code sql>
+SELECT T.TABLE_NAME, T.NUM_ROWS , C.COMMENTS
+FROM USER_TABLES T, USER_TAB_COMMENTS C
+WHERE C.TABLE_NAME(+)=T.TABLE_NAME
+</code>
+==== 直近に実行されてSQLを取得する ====
+<code 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
+</code>
+しかし、SQL_TEXTは1000文字までしか保存されていないので、それ以上になる場合は上記SQLでSQL_IDがわかるのでそれを元に次のSQLを投げれる。
+<code sql>
+-- 例 sql_id:gdhax8h3wx54r
+SELECT
+sql_text
+FROM
+v$sqltext
+WHERE
+sql_id = 'gdhax8h3wx54r'
+ORDER BY piece
+</code>
+しかし、分割されて行にセットされるため。\\
+結合する際に、末尾の空白が混在してしまうことに気をつけること。
+  * [[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 にはデータが存在しない。
+<code sql>
+-- 統計情報の更新
+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 = '(インデックス名)'
+</code>
+  * [[http://tihiro.hatenablog.com/entry/2017/08/31/120917|Oracleでインデックスの断片率を確認と再構築について。]]
+  * [[http://fulfillment-c.com/database/index-fragmentation-stats/|索引（インデックス）の断片化の確認方法]]
+==== 表領域を減らす ====
+=== 対応1 ===
+不要なユーザーを削除する。それと一時表領域を縮小するために下記を実行する。
+<code>
+select * from dba_temp_free_space;
+alter tablespace TEMP shrink space keep 500M;
+</code>
+[[https://blog.wackwack.net/entry/2016/09/17/221810|ディスクを圧迫した一時表領域をオンラインのまま縮小する]]
+=== 対応2 ===
+UNDO領域を縮小する。\\
+[[https://yohei-a.hatenablog.jp/entry/20090804/1249380350|肥大した UNDO 表領域を縮小する]]
+<code>
+-- システムユーザーで接続する
+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 を削除する。
+</code>
+==== 大量データの削除 ====
+大量データを削除する場合、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.*」となります。
+===== データプロバイダが見つからない ======
+bit PCにて.NET framework 4.6を使用したアプリケーションで下記場所で「.Net Frameworkデータプロバイダが見つからない」エラーが発生する。
+<code vbnet>
+DbProviderFactories.GetFactory("Oracle.DataAccess.Client")
+</code>
+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を使う インストール方法]]
+<code>
+install.bat odp.net4 [ORACLE_HOMEのパス] [ORACLE_HOME名] true
+</code>
+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 を含みます。|