データディクショナリとは

Oracle Databaseのデータディクショナリとは、データベース内のオブジェクトに関するメタデータを集積した表のことです。
SQL Developerの接続ナビゲータからテーブルやビューの一覧を表示できますが、それらの情報はGUIだけでなく、データディクショナリを活用することでSQLによって取得することも可能です。

データディクショナリには膨大な種類が存在します。
そこで、本記事では「初心者にもぜひ知っておいてほしい」ものに絞って紹介します。

本記事を読むにあたっての注意事項

本記事は、筆者の経験に基づいて作成されたものであり、Oracle社の公式見解ではありません
本記事の内容は、特定の企業やシステムに依存しない汎用的なテクニックに関する情報です
サンプルクエリはOracle Database 23ai Freeで動作を確認していますので、他のバージョンでは動作しない可能性があります

本記事で扱うデータディクショナリ

名称	利用頻度
USER_OBJECTS	多い
USER_TABLES	多い
USER_TAB_COMMENTS	少ない
USER_TAB_COLUMNS	多い
USER_COL_COMMENTS	普通
USER_INDEXES	少ない
USER_IND_COLUMNS	多い
USER_SYNONYMS	普通
USER_VIEWS	普通
USER_DB_LINKS	普通

接頭辞について

本記事で扱うデータディクショナリはすべて「USER」が接頭辞になっていますが、接頭辞が「ALL」や「DBA」となるデータディクショナリもあり、それらを使用すると接続しているスキーマ以外のオブジェクト情報を検索できます。

ただし、権限やロールの設定次第では、特に接頭辞が「DBA」となっているオブジェクトは参照できない場合がある点に留意しましょう。
一方、「USER」が接頭辞のデータディクショナリでは、接続しているスキーマが所有するオブジェクトのみを検索できますが、権限やロールに関係なく参照可能です。

USER_OBJECTS

TABLEやVIEWなどのオブジェクトのメタデータを持つデータディクショナリです。
例えば、どのオブジェクトがいつ作成されたか、DBオブジェクトのリプレースによって状態がINVALIDになっていないかなどを確認できます。

サンプルクエリ

直近1年間に作成されたDBオブジェクトを最新順に検索するクエリです。

SELECT
    object_name
   ,object_type
   ,created
FROM
    user_objects
WHERE
    CREATED >= TRUNC(SYSDATE) - 365
ORDER BY
    created DESC
;

USER_TABLES

テーブルのメタデータを持つデータディクショナリです。
統計情報を取得した日時や統計情報そのものを確認できます。

サンプルクエリ

統計情報を元に、レコード数が1,000万件以上のテーブルを検索するクエリです。

SELECT
    table_name
   ,num_rows
   ,blocks
   ,last_analyzed
FROM
    user_tables
WHERE
    num_rows >= 10000000
ORDER BY
    num_rows DESC
;

USER_TAB_COMMENTS

以下のコマンドで付与された、テーブルに対するコメントを持つデータディクショナリです。

COMMENT ON TABLE schema.table_name IS 'table_comment'

サンプルクエリ

テーブルのコメントから、受注情報に関係しそうなテーブルの一覧を取得するクエリです。

SELECT
    table_name
   ,comments
FROM
    user_tab_comments
WHERE
    comments LIKE '%受注%'
ORDER BY
    table_name
;

USER_TAB_COLUMNS

テーブルに定義されているカラムの情報を持つデータディクショナリです。
カラムのデータ型やNULLの許容可否などを確認できます。

サンプルクエリ

ORDERSテーブルでNOT NULL制約が設定されているカラムを取得するクエリです。

SELECT
    column_name
   ,data_type
   ,column_id
FROM
    user_tab_columns
WHERE
    table_name = 'ORDERS'
AND nullable = 'N'
ORDER BY
    column_id
;

column_idは、SELECT *と記載した際に表示されるカラムの順序で使用されています。

USER_COL_COMMENTS

以下のコマンドで付与された、カラムに対するコメントを持つデータディクショナリです。

COMMENT ON COLUMN schema.table_name.column_name IS 'column_comment';

サンプルクエリ

システム内で扱う電話番号に国番号を付与する目的で、桁数拡張が必要になりそうなカラムを検索するクエリです。

SELECT
    table_name
   ,column_name
   ,comments
   ,data_type
   ,char_length
   ,char_used
FROM
    user_col_comments
NATURAL JOIN
    user_tab_columns
WHERE
    comments LIKE '%電話番号%'
ORDER BY
    table_name
   ,column_id
;

データディクショナリを検索する際は、大抵の場合開発工程以前での調査、分析目的のため、可能な限り記述量を減らしたいところです。
データディクショナリは、普段あまり使わないNATURAL JOINを利用できることが多いので、結合時のON句を省略することで手っ取り早いクエリ記述ができます。

下記記事も是非ご参考ください。

USER_INDEXES

インデックスのメタデータを持つデータディクショナリです。
統計情報を取得した日時や、インデックスの種類、ユニーク制約の有無などを確認できます。

サンプルクエリ

ORDERSテーブルに付与されているINDEXを検索するクエリです。

SELECT
    index_name
   ,index_type
   ,last_analyzed
FROM
    user_indexes
WHERE
    table_name = 'ORDERS'
ORDER BY
    index_name
;

USER_IND_COLUMNS

インデックスに含まれるカラムの構成を持つデータディクショナリです。

サンプルクエリ

ORDERSテーブルに作成されているインデックスと、そのカラムを検索するクエリです。

SELECT
    index_name
   ,column_position
   ,column_name
FROM
    user_ind_columns
WHERE
    table_name = 'ORDERS'
ORDER BY
    index_name
   ,column_position
;

USER_SYNONYMS

シノニムの参照先を持つデータディクショナリです。

サンプルクエリ

在庫（INV）スキーマのオブジェクトを参照しているシノニムを検索するクエリです。

SELECT
    synonym_name
   ,table_name
FROM
    user_synonyms
WHERE
    table_owner = 'INV'
ORDER BY
    synonym_name
;

USER_VIEWS

ビューの属性（READ ONLY等）や、ビューへアクセスした際に実行されるSQLを持つデータディクショナリです。

サンプルクエリ

ビュー「MONTHLY_ORDER_SUMMARY」へアクセスした場合に、実行されるSQLを取得するクエリです。

SELECT
    view_name
   ,text
FROM
    user_views
WHERE
    view_name = 'MONTHLY_ORDER_SUMMARY'
;

「TEXT」カラム、「TEXT_VC」カラムについて

SQLが設定されているカラム「TEXT」は、LONG型のためWHERE句内で絞り込みができません。「TEXT_VC」カラムはVARCHAR2なのでWHERE句での使用が可能ですが、デフォルトでは4000バイトまでしか設定されません。
よって、VIEWが参照しているオブジェクトを検索する際に、TEXT_VCに対してLIKE検索すると期待通りの結果を得られない可能性があります。
オブジェクト同士の依存関係を確認したい場合は、USER_DEPENDENCIESを検索しましょう。

＜参考＞

USER_DB_LINKS

DBリンクオブジェクトの情報を持つデータディクショナリです。

サンプルクエリ

DBリンクの一覧を検索するクエリです。

SELECT
    *
FROM
    USER_DB_LINKS
;

APPENDIX

本記事で紹介したデータディクショナリはごく一部です。
また、記事内で触れているデータディクショナリの項目も同様にごく一部です。
より詳細なデータディクショナリは下記公式サイトで確認してください。