[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の占有率を調べることができれば解決できそうだけど・・・