[Oracle]Materialized ViewのORA-12899

▼問題
特定の条件を満たすと、Materialized Viewリフレッシュ時にORA-12899: 列 string の値が大きすぎます（実際: string、最大: string)が発生する。

▼対処方法
元表の項目桁数変更時はMaterialized Viewを再作成することで回避可能。

▼原因
Materialized View作成後、元表の項目桁数を拡張した際に発生する。これは、Materialized Viewの項目定義が、元表変更後の項目と一致しないために発生するようである。代表として桁数の観点で検証を実施したが、桁数以外にも当てはまると思われる。（例えば、Materialized Viewのカラム宣言でSELECT * ～を指定している場合など）

▼再現手順
--テーブル作成
create table tab(
col1 varchar2(1)
);


--データ投入
insert into tab
(col1)
values
('A')
;


--Materialized Viewを作成する
create materialized view mv_tab
refresh with rowid
as
select * from tab;


--この段階ではリフレッシュ可能である
begin dbms_mview.refresh('mv_tab'); end;
/
PL/SQLプロシージャが正常に完了しました。


--元表の定義を変更する
alter table tab modify(col1 varchar2(2));


--この時点でもリフレッシュ可能である
begin dbms_mview.refresh('mv_tab'); end;
/
PL/SQLプロシージャが正常に完了しました。


--データを変更する（1桁→2桁）
update tab
set col1 = 'AB'
;


--この時点でリフレッシュ不可能となる
begin dbms_mview.refresh('mv_tab'); end;
/

begin dbms_mview.refresh('mv_tab'); end;
*
行1でエラーが発生しました。:
ORA-12008: マテリアライズド・ビューのリフレッシュ・パスでエラーが発生しました。
ORA-12899: 列"FXUSER"."MV_TAB"."COL1"の値が大きすぎます(実際: 2、最大: 1)
ORA-06512: "SYS.DBMS_SNAPSHOT", 行2255
ORA-06512: "SYS.DBMS_SNAPSHOT", 行2461
ORA-06512: "SYS.DBMS_SNAPSHOT", 行2430
ORA-06512: 行1



[Appendix]
USER_TAB_COLUMNS表を確認したところ、Materialized Viewは実体表なので、内部的にはテーブルとして存在しているらしく、Materialized Viewと同名のオブジェクトが存在した。Materialized Viewのリフレッシュ時にはINSERTを実行しているため、トレースするまでもなくTAB→MV_TABへのデータ伝播（INSERT）の際に標記のエラーが発生したと見て間違いなさそうだ。
定義は以下の通り。

★エラーが発生しないときの定義
TABLE_NAME COLUMN_NAME DATA_LENGTH
----------- -------------- -----------
MV_TAB COL1 1
TAB COL1 1

★エラーが発生したときの定義
TABLE_NAME COLUMN_NAME DATA_LENGTH
----------- -------------- -----------
MV_TAB COL1 1
TAB COL1 2

[Oracle][SQL]テンポラリテーブルについて

リカバリ不要なワークテーブルとかに使う。

＜特徴＞
・REDOログを生成しないので高速にDML処理できる。
・生成したレコードは、生成したセッションからのみ参照できる。
・コミット後もしくはセッション終了後、データは削除（TRUNCATE）される。
・一時表領域を使用する。


＜制限事項＞
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

＜定義登録方法＞
▼SQL文
CREATE GLOBAL TEMPORARY TABLE 表１
(
　列名　データ型...
)
ON COMMIT[DELETE ROWS | PRESERVE ROWS]
;

▼テンポラリテーブルのレコードをクリアするタイミングを設定する。
・DELETE ROWS
トランザクション終了時にデータをクリアする。

・PRESERVE ROWS
セッション終了時にデータをクリアする。
※外部アプリがDB接続をコネクションプーリングしている場合、要注意かも。


[Appendix]
・統計情報はどうなる？

[Oracle][SQL]外部結合に関する制約

外部結合演算子(+)を使用する際には、色々結合条件に制約がある。
今日は、たまたまぶちあたった問題についてのメモ。
結合条件に複数項目を指定してる場合、全て外部結合にしなくてはならない。


▼表定義
例えば、こんな表があったとする。

[表１]
CREATE TABLE EMP(
EMP_CD CHAR(8),
EMP_NAME VARCHAR2(100),
DEPT_CD CHAR(8),
DELETE_FLG CHAR(1)
);

[表２]
CREATE TABLE DEPT(
DEPT_CD CHAR(8),
DEPT_NAME VARCHAR2(100),
DELETE_FLG CHAR(1)
);


▼問題点
で、こんなSQLを実行したとする。

[問題のSQL]
1 SELECT
2 E.EMP_CD,
3 D.DEPT_NAME
4 FROM
5 EMP E,
6 DEPT D
7 WHERE E.DEPT_CD = D.DEPT_CD(+)
8 AND D.DELETE_FLG = '0'
9 ;

この場合、外部結合にしたくても、内部結合となってしまう。
EMP表のDEPT_CDが、DEPT表に存在しない場合でもEMP表のレコードは表示したい、
という意図で、7行目に「e.DEPT_CD = d.DEPT_CD(+)」外部結合として記述したが、
8行目の「d.DELETE_FLG = '0'」により外部結合の要件を満たさなくなる。


▼解決方法
で、問題のSQLをうまいこと外部結合にしたい場合はこう書く必要がある。

[解決のSQL]
1 SELECT
2 e.EMP_CD,
3 d.DEPT_NAME
4 FROM
5 EMP e,
6 (SELECT * FROM DEPT WHERE DELETE_FLG ='0') d
7 WHERE e.DEPT_CD = d.DEPT_CD(+)
8 ;

ということで、今回の様に、予め特定条件でフィルタできる場合は
FROM句にてインラインビューとして取得するのが吉。


▼APPENDIX
その他の制限については今後加筆予定。

[Oracle]Materialized View を高速に完全リフレッシュ

以下のコマンドにより、非常に高速に完全リフレッシュを行うことができる。

[コマンド]
DBMS_MVIEW.REFRESH('MVEW_NAME','c',NULL,TRUE,FALSE,1,0,0,FALSE,FALSE);

[注意]
リフレッシュ処理の間、他トランザクションから当該Materialized Viewを参照した場合、0件として表示されるため、読み取り一貫性を保障する必要がある場合注意が必要である。

[方式]
通常の完全リフレッシュでは、Materialized Viewは実体を持つ（結局はテーブルなのだけれど）ため、実体に対してDELETE→INSERT処理となる処理を、TRUNCATE→INSERT処理に変更してリフレッシュする。

なぜ、上記コマンドでDELETEからTRUNCATE処理に変更されるかについては、今後追記する。オンライン開局中よりもオフライン処理中に使うことが多いかもしれない。

[Oracle]PL/SQLにおける自律型トランザクション宣言

プロシージャ中に以下の宣言を追加することで、プロシージャを自律型トランザクションを定義することができる。プロシージャのみ独立してトランザクションを完結したい場合に使用する。逆に、自律型トランザクションでないプロシージャ内で、トランザクションを確定した場合、呼び出し元のトランザクションも確定するので注意。

[実装方法]
・自律型トランザクションプラグマを宣言する。
→PRAGMA AUTONOMOUS_TRANSACTION;

[メリット]
・呼び出し元のトランザクションに依存せずに処理を進めることができる。
→例）ログテーブルにエラーメッセージを出力する。
・ロック時間の短縮が期待できる
→プロシージャ中のDMLが対象とするオブジェクトのロック時間が短くなる。
・SELECT文中でも使用できる
→通常、SELECT文中で呼び出す関数にDMLが含まれるとエラーとなる。

[制限]
・プロシージャ中でトランザクションを確定する必要がある。
→トランザクションが確定しない場合、処理はロールバックし、呼び出し元に例外が通知される。

[サンプル]
CREATE OR REPLACE PROCEDURE PRC_LOGOUTPUT(
Pi_vMSG IN VARCHAR2,
Pi_vPG_ID IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION; --プラグマ宣言
BEGIN
INSERT INTO LOGTABLES(MSG, PG_ID, CREATED_ON)
VALUES (Pi_vMSG, Pi_vPG_ID, SYSDATE);
COMMIT; --トランザクションの確定
END;
/

[Oracle]OPatchを使用した個別パッチの適用メモ

先日Oracleに個別パッチを適用したので次回の為にメモを残す。

[手順]
・「opatch lsinventory -detail」にてOracleのパッチ状況を調べる。
・Enterprise Manager Database ConsoleをSTOPする。
・LISTENERをSTOPする。
・インスタンスをSHUTDOWNする。
・OPatchを適用する。
・「opatch lsinventory -detail」にてパッチが適用されたことを確認する。
・インスタンスをSTARTUPする。
・LISTENERをSTARTする。
・Enterprise Manager Database ConsoleをSTARTする。

[備考]
・OPatchは「$ORACLE_HOME/OPatch」に存在する。※注：下記項目参照
・↑のものは古い可能性がある。OPatchは常に最新版のものを使用すること。
・個別パッチ解凍先に移動した状態でOPatchを起動する。

[Oracle]現在使用されているオブジェクトを調べる

ロックされているオブジェクトを調べるのは簡単だど、単純にSELECTされている表などを調べることができないかと思い、色々攻めあぐねた結果、以下のSQLでそれらしい情報を取得できることが判明した。但し、CRUD等どのような操作を行っているかまでは判別できないみたい。（v$sql表と組み合わせてSQL文見れば良いかもだけど、構文解析が大変そう＆信頼性がどうなの＆処理が重そうなのでやめた）

- - - - - - - - - - - - - - - - - - - - - - - -
select

s.sid --SID

,s.serial# --シリアル番号

,s.username --Oracleログインユーザ名

,o.to_owner --使用してるオブジェクトオーナー

,o.to_name --使用してるオブジェクト名

from v$session s, v$object_dependency o

where s.sql_address = o.from_address

and s.sql_hash_value = o.from_hash

- - - - - - - - - - - - - - - - - - - - - - - -


これは例えば、次の様な状況のときに使用できるんじゃないかな。
僕のライフワークであるMaterialized Viewでのケースを例にしましょうね。


[状況]
・オンライン開局中にMaterialized ViewをTRUNCATEモードでリフレッシュしなければならない。
・リフレッシュ対象のMaterialized Viewに対する他セッションからの読み取り可能性は常にある。

[問題]
・Materialized ViewをTRUNCATEモードでリフレッシュすると、読取一貫性が保証できなくなる。
→他のセッションがMaterialized ViewをSELECTしていた場合でも問答無用でTRUNCATEするからである。これはMaterialized ViewをSELECTしている最中にTRUNCATEモードでのリフレッシュが走ると、TRUNCATEされた時点までのレコードしか読み取らない（しかもエラーにならない）。例えば本来100万件保持するMaterialized Viewなのに、10万件しか読み取らなかったというケースが発生し業務的にありえない状況になる。

[解決方法]
先に紹介したSQLから取得された情報を使用して、Materialized Viewのリフレッシュ処理部品中に、リフレッシュ対象のMaterialized Viewが他セッションから読み取られている場合に、Materialize Viewリフレッシュ処理をWAITする、処理を実装することで回避できる。（とはいいつつもMaterialized ViewをTRUNCATEモードでリフレッシュ中に、他のセッションがSELECTしたら0件が返却されるから、今度は読取セッション側でMaterialized Viewがリフレッシュ中かどうかの調べるロジックが必要になるという罠）

[Oracle]sqlplusw.exeが...いない

自宅のPCではOracle10gExpressEdition環境ですが、sqlplusw.exeがないのです。sqlplus.exeはあるのだけれど。ExpressEditionならではの制限事項なのかな、と思っていたら、sqlplusw.exeはOracle11gR1で廃止されているようです。Oracle10gExpressEditionでsqlplusw.exeが無いのはこの流れを先取りしてるからなのかしらね。

Oracle Database 11gリリース1（11.1）で廃止になったコンポーネント

Oracle Database 10gリリース2（10.2）に付属していた次のコンポーネントは、Oracle Database 11gのインストールでは使用できません。

iSQL*Plus

Oracle Workflow

Oracle Data Mining Scoring Engine

Oracle Enterprise Manager Javaコンソール

SQL*Plusグラフィック・ユーザー・インタフェース

Oracle Enterprise Manager Javaコンソールも廃止されちゃうのか。iSQL*PlusとかOracle Workflowはまあ当然の流れだとしても、sqlplusw.exeとOracle Enterprise Manager Javaコンソールも廃止されるのは切なさ炸裂ですね。sqlplusw.exeのアイコンちょっと可愛かったのに･･･

[Oracle]データベースのバージョンを調べる

サポートセンターに問い合わせる際に、Oracleのバージョンを尋ねられるが、流石に詳細なバージョンまで把握していない。そんな時はこれを使う。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
[SQL]
SELECT * FROM V$VERSION;

[結果]
BANNER
-----------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ふとした疑問。EXPRESS EDITIONでもお金払えばサポートしてくれるのかな？

[Oracle]Materialized View-高速リフレッシュ時のORA-00001

「Materialized Viewリフレッシュ処理の夜間バッチがABENDしたんだけど」
で、調べてみたら一意制約違反（ORA-00001）が発生して後続ジョブが全部死んでた。Materialized View Logを見ても、特に一意制約違反になりそうな内容でない、というか単一テーブルのレプリカだから一意制約違反になるはずもなく。

で、サポートに問い合わせたところ、Materialized Viewの仕様だった。高速リフレッシュ方式のMaterialized ViewにPrimary Key制約はつけちゃダメだって。Oracle7からの仕様らしい。どこに載ってるんだ。ていうか索引作成する段階でエラーにしてくれりゃいいのに。

＜結論＞
高速リフレッシュ方式によるMaterialized Viewにおいて、Primary Key制約及び一意索引（UNIQUE INDEX）はサポートされていない。

＜理由＞
Materialized View Logが適用される順序は時系列順とは限らない。
だからこんなんでも、エラーになるそうでつ。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--テーブルの作成
CREATE TABLE TBL_TEST(INS_CODE CHAR);
ALTER TABLE TBL_TEST ADD CONSTRAINT PK_TBL_TEST PRIMARY KEY (INS_CODE);

--Materialized View Logの作成
CREATE MATERIALIZED VIEW LOG ON TBL_TEST WITH ROWID;

--Materialized Viewの作成
CREATE MATERIALIZED VIEW MV_TEST
REFRESH FAST WITH ROWID
ON DEMAND
AS
SELECT
　INS_CODE
FROM TBL_TEST
;
ALTER TABLE MV_TEST ADD CONSTRAINT PK_MV_TEST PRIMARY KEY (INS_CODE);

--データ投入→削除→投入
INSERT INTO TBL_TEST VALUES('A');
DELETE TBL_TEST;
INSERT INTO TBL_TEST VALUES('A');

--Materialized Viewのリフレッシュ
begin dbms_mview.refresh('MV_TEST','f'); end;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
＜対策＞
いずれかの対策により回避する。
・PRIMARY KEY制約及び一意索引を削除する。
・高速リフレッシュ方式から完全リフレッシュ方式に切り替える。
・マスター表に「遅延可能なPRIMARY KEY制約」を作成する（なんのこっちゃ）

で、結局プロジェクトとしては、制約を削除してよいものは削除して、どうしてもPRIMARY KEY制約が必要なMaterialized Viewは完全リフレッシュとする方針となりました。今度個人的に「遅延可能なPRIMARY KEY制約」を検証してみよう。
こんなのがユーザ受け入れテスト段階で出るなんて・・・もっと早く出てよ。

[Oracle]Materialized ViewのCOMPILATION_ERROR

Materialized Viewの問い合わせ文中にINNSER JOIN句による内部結合をすると、Materialized Viewをリフレッシュした際にUSER_MVIEWS表のSTALENESS項目が「COMPILATION_ERROR」となることがこの度判明した。毛穴から2本以上生える毛のメカニズムには及ばないものの、やはり気になる。

で、この「COMPILATION_ERROR」状態のMaterialized Viewを検証してみたところ、リフレッシュ機能自身には問題ない様だが、何らかの潜在的なリスクを内包している恐れがあり、有事の際にOracle社に動作保証しませんとか言われると、もれなく窓の無い部屋で地球防衛軍総司令官になれちゃう。

したがって問い合わせ句を以下の通り修正した。（SQLはイメージ）

----------------------------------------
[修正前]
SELECT EMP_CD
FROM EMP E
INNER JOIN(
SELECT * FROM DEPT
) D
ON E.DEPT_CD = D.DEPT_CD
----------------------------------------
[修正後]
SELECT EMP_CD
FROM(
SELECT E.EMP_CD
FROM EMP E, DEPT D
WHERE E.DEPT_CD = D.DEPT_CD
)
----------------------------------------

いや、何ということは無く、ANSI標準の内部結合からOracle独自の内部結合方式としただけである。
これだけで不思議と「COMPILATION_ERROR」も無くなりきちんと「FRESH」となるだけでなく、REWRITE_CAPABILITY項目も「GENERAL」になるし良いことだらけです。

Oracleの内部結合ではANSI構文が後付けでサポートされたからこうなってるのかなあ。それってどうなのよ。

[Oracle]<性能>DBを重くしているセッションを突き止める - 初級編

よくアプリチームから「何かDB重いんだけど調べてくれない？」と言われるあなた。
下っ端だから話しかけやすいんだろうね。リーダーは今日もむっつり忙しそう。
該当のセッションから犯人をお手軽突き止める方法のメモ。


[前提]
以下の設定が必要（意識してなかった）
・コストベース・オプティマイザを使用を使用している。
・TIMED_STATISTICSまたはSQL_TRACEパラメータをtrueに設定している。
・ANALYZE文またはDBMS_STATSパッケージで、使用するオブジェクトの統計情報を収集する。


[手順]
▼方法１．Enterprise Managerでセッション一覧を表示して「長時間処理」項目に該当するセッションを調べる。端末IDとかユーザIDも一発で調べられて便利
▼方法２．「select * from v$session_longops」を発行して長時間セッションが無いかどうか調べる。以下の項目を見れば現在暴れてるセッションがたぶん一目瞭然。
・START_TIME…操作の開始時刻
・TIME_REMAINING…操作が終了するまでの残りの推定時間（秒）
・ELAPSED_SECONDS…操作の開始からの経過時間（秒）
※v$session_longops…6秒以上掛かる処理（1処理単位）を抽出する動的パフォーマンスビュー。Enterprise Managerはよくわからんけど結局「方法１」に出てきたv$session_longops見てるんだろうなあ


[難点]
1処理単位（トランザクション単位ではない点に注意）で6秒以上の処理しか補足できないので、天文学的な細かい処理を発行しているセッションは長時間処理として捕捉できない。OS上、ディスクの%busyは100で完全にOracleの仕業なのにセッションレベルでの原因究明できずDBAチームとして情けない気分になることうけあい。セッション単位でディスクI/Oの占有率を調べることができれば解決できそうだけど・・・