使っていきたいディクショナリビュー一覧

# はじめに
　普段DBA業務を担当しています。そのDBA業務でよく調査等に使用する「ディクショナリビュー」について、よく使うものの中から、DBA権限がないユーザでも使えそうなものをピックアップし概要や応用をメモしていきます。
# 環境
・Oracle Database 19c
# ディクショナリビューとは
正確には「静的データ・ディクショナリ・ビュー」で、テーブル/ビュー/権限/サイズなど、データベース内オブジェクトや関連する情報を確認できる。
変更があるたびにディスクに書きこまれ保存される（静的）。
反対に、「動的パフォーマンスビュー」というメモリに情報を持っており常に最新の情報を確認するビューもある。
# よく使うディクショナリビュー
オブジェクト調査以外にも、データベース内オブジェクトへの変更(DDL)を実施する際、前後確認や切り戻し手順確立の為にも活用。
「ALL_」：現行のユーザーがアクセス可能な情報をすべて表示。他ユーザでも権限があれば確認可能。
「USER_」：現行のユーザーの情報（スキーマ内オブジェクト）
|ディクショナリビュー|概要|
|:-----------|:-----------|
|ALL_OBJECTS|オブジェクト一覧/オブジェクトタイプ(TABLE/SYNONYM等)<br>作成日、最終定義更新日等|
|ALL_TABLES|テーブル一覧/レコード数/統計情報取得日/表領域<br>※レコード数は統計情報取得時に更新されるため、<br>ずっと取得していない場合は値が古い可能性がある|
|ALL_TAB_COLUMNS|テーブルのカラム情報(型/長さ等)一覧|
|ALL_TAB_COMMENTS|テーブルコメント一覧|
|ALL_COL_COMMENTS|カラムコメント一覧|
|ALL_INDEXES|INDEX一覧|
|ALL_IND_COLUMNS|INDEXの列一覧<br>対象INDEXがどのカラムで構成されているか|
|ALL_CONSTRAINTS|制約/制約の種類|
|ALL_MVIEWS|MVIEW一覧/ソース/リフレッシュタイプ/リフレッシュ時間|
|ALL_PROCEDURES|ストアドプロシージャ一覧|
|ALL_SEQUENCES|シーケンス一覧。MIN,MAX|
|ALL_SYNONYMS|シノニム/参照先一覧|
|ALL_TAB_PRIVS|オブジェクト権限一覧|
|ALL_TRIGGERS|トリガー/起動イベント/テーブル/ソース|
|ALL_VIEWS|ビュー/ソース|
|ALL_SOURCE|ストアド系のソース|
|USER_SEGMENTS|ログインしているスキーマ内のオブジェクトサイズ確認|
|ALL_DEPENDENCIES|依存関係(トリガーがどこに影響あるのか/VIEW等がどこを参照しているのか)|
|ALL_TAB_MODIFICATIONS|表のレコードの変更件数（INSERT/UPDATE/DELETE）<br>※統計情報取得後～TIMESTAMP(前回表が更新された時間)列の時間の間の変更件数|

※下記でアクセス可能なディクショナリビューを全て確認可能。（通常ユーザで約1000あるため参考程度に）
```
select * from DICTIONARY;
```
# 個人的に重宝しているディクショナリビュー３選
## USER_SEGMETS
テーブルサイズや断片化が進んでいないか確認する際に使用。
### 事象例
対象スキーマの表領域が枯渇した
⇒肥大化しているテーブルが無いか/肥大化していないかを下記SQLで確認。
```
SELECT
    o.object_name,
    o.object_type,
    s.tablespace_name,
    s.bytes / 1024 / 1024 mb,
    o.created,
    o.last_ddl_time
FROM
    user_segments   s,
    user_objects    o
WHERE
    o.object_name = s.segment_name
ORDER BY
    bytes DESC;
```
### 出力例
|OBJECT_NAME|OBJECT_TYPE|TABLESPACE_NAME|MB|CREATED|LAST_DDL_TIME|
|:-----------|:-----------|:-----------|:-----------|:-----------|:-----------|
|TABLE_A|TABLE|TBS01|0.75|21-10-29|22-05-30|
|INDEX_A|INDEX|TBS01|0.125|21-10-29|22-05-30|
|INDEX_B|INDEX|TBS01|0.125|21-10-29|22-05-30|

**OBJECT_NAME**：オブジェクト名
**OBJECT_TYPE**：オブジェクトタイプ（TABLE/INDEX等）
**TABLESPACE_NAME**：表領域名
**MB**：サイズ（MB）
**CREATED**：オブジェクト作成日
**LAST_DDL_TIME**：定義変更日

## ALL_TAB_MODIFICATIONS
テーブルの更新頻度や現在のレコード数を調べたい場合等に使用。
### 事象例
INDEXやカラムの追加時にテーブルをロックし更新のセッションが滞留してしまうことがあり、
秒に数百レベルの更新のあるテーブルに対しINDEXを追加した際、負荷が急騰してしまった。
⇒下記SQLでレコード数、更新頻度を確認し、多い場合は時間外作業を検討。
```
SELECT
    md.table_owner,
    md.table_name,
    tb.num_rows - md.deletes + md.inserts records,
    md.inserts,
    md.updates,
    md.deletes,
    TO_CHAR(md.timestamp, 'YYYY/MM/DD HH24:MI:SS') "TIMESTAMP",
    TO_CHAR(tb.last_analyzed, 'YYYY/MM/DD HH24:MI:SS') "LAST_ANALYZED",
    tb.tablespace_name
FROM
    all_tab_modifications md
    LEFT OUTER JOIN all_tables tb ON ( md.table_owner = tb.owner AND md.table_name = tb.table_name )
WHERE
    md.table_owner = 'スキーマ名'
    AND md.table_name = 'テーブル名'
;
```
### 出力例
|TABLE_OWNER|TABLE_NAME|RECORDS|INSERTS|UPDATES|DELETES|TIMESTAMP|LAST_ANALYZED|TABLESPACE_NAME|
|:-----------|:-----------|:-----------|:-----------|:-----------|:-----------|:-----------|:-----------|:-----------|
|SCHEMA_A|TABLE_A|1276117|431030|2062283|126784|2022/05/30 17:23:11|2020/03/09 11:04:23|TBS01|

**TABLE_OWNER/TABLE_NAME**：スキーマ/テーブル名
**RECORDS**：現在のレコード数（に近いもの）
**INSERTS/UPDATES/DELETES**：「LAST_ANALYZED」から「TIMESTAMP」までの間の各レコード変更数
**TIMESTAMP**：前回表に変更のあった日時
**LAST_ANALYZED**：テーブルの統計情報取得日時

## ALL_DEPENDENCIES
あるテーブルをどんなオブジェクト（SYNONYM/VIEW/TRIGGER等）が参照しているかを確認
※特に下記紹介のSQLで出力される **<font color="Red">dependency_list</font>** カラムの情報は、階層的に依存関係を確認することができるので便利
### 事象例
あるテーブルのカラム削除を行った結果、MVIEWがリフレッシュ不可/使用不可になってしまった
⇒下記SQLでテーブルの依存関係を出力し、「STATUS」が「INVALID（無効化）」になっているオブジェクトについて、
　コンパイルや定義変更を行う。事前に影響のある箇所を確認。
```
SELECT
    dep.referenced_owner
    || '.'
    || dep.referenced_name AS refobj,
    dep.referenced_type,
    dep.owner
    || '.'
    || dep.name AS object,
    dep.type,
    obj.status,
    DECODE(level, 1, '直接', '間接') AS "dependency",
    substr(sys_connect_by_path(dep.referenced_owner
                               || '.'
                               || dep.referenced_name, '←'), 2)
    || '←'
    || dep.owner|| '.'
    || dep.name AS "dependency_list", TO_CHAR(obj.last_ddl_time, 'YYYY/MM/DD HH24:MI:SS') last_ddl
FROM
    all_dependencies dep,
    all_objects obj
WHERE
    ( dep.owner = obj.owner
      AND dep.name = obj.object_name
      AND dep.type = obj.object_type )
START WITH ( ( dep.referenced_owner = 'スキーマ名'
               AND dep.referenced_name = 'オブジェクト名' ) ) 
               CONNECT BY PRIOR dep.owner = dep.referenced_owner
                    AND PRIOR dep.name = dep.referenced_name
                    AND PRIOR dep.type = dep.referenced_type;
```
### 出力例
|REFOBJ|REFERENCED_TYPE|OBJECT|TYPE|STATUS|dependency|dependency_list|
|:-----------|:-----------|:-----------|:-----------|:-----------|:-----------|:-----------|
|SCHEMA_A.TABLE_A|TABLE|SCHEMA_B.XXXXX|SYNONYM|VALID|直接|SCHEMA_A.TABLE_A←SCHEMA_B.XXXXX|
|SCHEMA_A.TABLE_A|TABLE|SCHEMA_C.XXXXX|SYNONYM|VALID|直接|SCHEMA_A.TABLE_A←SCHEMA_C.XXXXX|
|SCHEMA_C.XXXXX|SYNONYM|SCHEMA_C.TRIGGER_C|TRIGGER|VALID|間接|SCHEMA_A.TABLE_A←SCHEMA_C.XXXXX←SCHEMA_C.TRIGGER_C|

**REFOBJ**：参照されている側のオブジェクトスキーマ.名称
**REFERENCED_TYPE**：参照参照されている側のオブジェクトのタイプ
**OBJECT**：参照している側のオブジェクトスキーマ.名称
**TYPE**：参照している側のオブジェクトのタイプ
**STATUS**：ステータス。VALID=有効、INVALID=無効。無効の場合はコンパイル等必要。
**dependency**：依存関係の形式。他のオブジェクトを仲介せずそのまま見ている場合は直接、そうでない場合は間接
**<font color="Red">dependency_list</font>**：依存関係の一覧。テーブルがどのように参照されているか確認可能。
<font color="Red"> ※依存関係については、DBA権限が無く全ての一覧を確認できず漏れが出る可能性があるため参考程度</font>
# 所感
・今回記載してるディクショナリビューの他にも、意外なものやこんなものまで？！という情報もあったりしたので、いつか使いどころ探してまとめてみたい。
・色々な調査がありその時で知りたい情報は異なるので、大体こんな名前のビューにはこんな情報がある、というものを把握してるとすぐに調査出来て使いやすい。
・今回紹介しているSQLは、下記参考サイトや元々チームで使っていたものを参考につぎはぎしていったものなので綺麗に整えたい
# 参考資料
・ORACLEの機能を使って、オブジェクト間の依存関係を調べる
　https://qiita.com/fujimohige/items/b6c4a6632116fb5d50a9
・静的データ・ディクショナリ・ビュー
　https://www.shift-the-oracle.com/view/data-dictionary-view/static-data-dictionary-view.html