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

はじめに

　普段DBA業務を担当しています。そのDBA業務でよく調査等に使用する「ディクショナリビュー」について、よく使うものの中から、DBA権限がないユーザでも使えそうなものをピックアップし概要や応用をメモしていきます。

環境

・Oracle Database 19c

ディクショナリビューとは

正確には「静的データ・ディクショナリ・ビュー」で、テーブル/ビュー/権限/サイズなど、データベース内オブジェクトや関連する情報を確認できる。
変更があるたびにディスクに書きこまれ保存される（静的）。
反対に、「動的パフォーマンスビュー」というメモリに情報を持っており常に最新の情報を確認するビューもある。

よく使うディクショナリビュー

オブジェクト調査以外にも、データベース内オブジェクトへの変更(DDL)を実施する際、前後確認や切り戻し手順確立の為にも活用。
「ALL_」：現行のユーザーがアクセス可能な情報をすべて表示。他ユーザでも権限があれば確認可能。
「USER_」：現行のユーザーの情報（スキーマ内オブジェクト）

ディクショナリビュー	概要
ALL_OBJECTS	オブジェクト一覧/オブジェクトタイプ(TABLE/SYNONYM等) 作成日、最終定義更新日等
ALL_TABLES	テーブル一覧/レコード数/統計情報取得日/表領域 ※レコード数は統計情報取得時に更新されるため、 ずっと取得していない場合は値が古い可能性がある
ALL_TAB_COLUMNS	テーブルのカラム情報(型/長さ等)一覧
ALL_TAB_COMMENTS	テーブルコメント一覧
ALL_COL_COMMENTS	カラムコメント一覧
ALL_INDEXES	INDEX一覧
ALL_IND_COLUMNS	INDEXの列一覧 対象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） ※統計情報取得後～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で出力される dependency_list カラムの情報は、階層的に依存関係を確認することができるので便利

事象例

あるテーブルのカラム削除を行った結果、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：依存関係の形式。他のオブジェクトを仲介せずそのまま見ている場合は直接、そうでない場合は間接
dependency_list：依存関係の一覧。テーブルがどのように参照されているか確認可能。
※依存関係については、DBA権限が無く全ての一覧を確認できず漏れが出る可能性があるため参考程度

所感

・今回記載してるディクショナリビューの他にも、意外なものやこんなものまで？！という情報もあったりしたので、いつか使いどころ探してまとめてみたい。
・色々な調査がありその時で知りたい情報は異なるので、大体こんな名前のビューにはこんな情報がある、というものを把握してるとすぐに調査出来て使いやすい。
・今回紹介しているSQLは、下記参考サイトや元々チームで使っていたものを参考につぎはぎしていったものなので綺麗に整えたい

参考資料

・ORACLEの機能を使って、オブジェクト間の依存関係を調べる
　https://qiita.com/fujimohige/items/b6c4a6632116fb5d50a9
・静的データ・ディクショナリ・ビュー
　https://www.shift-the-oracle.com/view/data-dictionary-view/static-data-dictionary-view.html