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