【Oracle】データディクショナリ完全ガイド｜DBA_* / ALL_* / USER_* ビューの使い方とよく使うクエリまで解説

-- 現在のユーザーが所有するテーブルのみ
SELECT table_name FROM USER_TABLES ORDER BY table_name;

-- 権限のあるすべてのテーブル（他スキーマ含む）
SELECT owner, table_name FROM ALL_TABLES ORDER BY owner, table_name;

-- データベース内のすべてのテーブル（DBA権限が必要）
SELECT owner, table_name FROM DBA_TABLES ORDER BY owner, table_name;

-- USER_* には owner 列がない（自分のオブジェクトのみなので自明）
-- ALL_* / DBA_* には owner 列がある

-- スキーマ内のオブジェクトを種類別に一覧する
SELECT
    object_name,
    object_type,     -- TABLE, VIEW, INDEX, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, SEQUENCE など
    status,          -- VALID または INVALID（コンパイルエラーがある場合）
    last_ddl_time,   -- 最後に DDL が実行された日時
    created          -- オブジェクトの作成日時
FROM USER_OBJECTS
WHERE object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER')
ORDER BY object_type, object_name;

-- INVALID なオブジェクトを検出する（コンパイルエラー・依存オブジェクトの変更で無効化）
SELECT object_name, object_type, status
FROM USER_OBJECTS
WHERE status = 'INVALID'
ORDER BY object_type, object_name;

-- オブジェクト数を種類別に集計する
SELECT object_type, COUNT(*) AS cnt
FROM USER_OBJECTS
GROUP BY object_type
ORDER BY cnt DESC;

-- 特定のオブジェクトが存在するか確認する
SELECT COUNT(*) FROM USER_OBJECTS WHERE object_name = 'EMPLOYEES' AND object_type = 'TABLE';
-- 1 なら存在する。0 なら存在しない

-- テーブルの基本情報（行数・ブロック数・最終統計収集日）
SELECT
    table_name,
    num_rows,            -- 直近の統計収集時の行数（DBMS_STATS で更新）
    blocks,              -- 使用ブロック数
    avg_row_len,         -- 平均行長（バイト）
    last_analyzed,       -- 最後に統計を収集した日時
    partitioned,         -- YES: パーティションテーブル
    iot_type,            -- INDEX ORGANIZED: IOT テーブル
    compression          -- ENABLED/DISABLED: テーブル圧縮の設定
FROM USER_TABLES
WHERE table_name = 'EMPLOYEES';

-- 列の定義を確認する
SELECT
    column_id,
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale,
    nullable,        -- Y: NULL 許可、N: NOT NULL
    data_default     -- デフォルト値
FROM USER_TAB_COLUMNS
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;

-- 制約の一覧を確認する
SELECT
    constraint_name,
    constraint_type,   -- P:主キー、U:一意、R:外部キー、C:チェック
    search_condition,  -- CHECK 制約の条件式
    r_owner,           -- 参照先スキーマ（外部キーの場合）
    r_constraint_name, -- 参照先の制約名（外部キーの場合）
    status,            -- ENABLED / DISABLED
    validated          -- VALIDATED / NOT VALIDATED
FROM USER_CONSTRAINTS
WHERE table_name = 'EMPLOYEES'
ORDER BY constraint_type, constraint_name;

-- 外部キーの参照関係を確認する
SELECT
    a.constraint_name      AS fk_name,
    a.table_name           AS child_table,
    b.table_name           AS parent_table,
    b.constraint_name      AS pk_name
FROM USER_CONSTRAINTS a
JOIN USER_CONSTRAINTS b ON a.r_constraint_name = b.constraint_name
WHERE a.constraint_type = 'R'
ORDER BY a.table_name;

-- インデックスの一覧
SELECT
    index_name,
    index_type,       -- NORMAL, BITMAP, FUNCTION-BASED NORMAL など
    uniqueness,       -- UNIQUE / NONUNIQUE
    status,           -- VALID / UNUSABLE
    num_rows,
    last_analyzed,
    partitioned,
    visibility        -- VISIBLE / INVISIBLE（12c 以降）
FROM USER_INDEXES
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name;

-- インデックスの構成列を確認する
SELECT
    i.index_name,
    c.column_position,
    c.column_name,
    c.descend         -- ASC / DESC
FROM USER_INDEXES i
JOIN USER_IND_COLUMNS c ON i.index_name = c.index_name
WHERE i.table_name = 'EMPLOYEES'
ORDER BY i.index_name, c.column_position;

-- テーブルの主キーを構成するインデックスを確認する
SELECT
    c.constraint_name,
    i.index_name,
    ic.column_name,
    ic.column_position
FROM USER_CONSTRAINTS c
JOIN USER_INDEXES i      ON c.index_name = i.index_name
JOIN USER_IND_COLUMNS ic ON i.index_name = ic.index_name
WHERE c.table_name       = 'EMPLOYEES'
  AND c.constraint_type  = 'P'
ORDER BY ic.column_position;

-- PL/SQL オブジェクトのソースコードを確認する
SELECT line, text
FROM USER_SOURCE
WHERE name = 'PROCESS_ORDER'   -- プロシージャ/関数/パッケージ名（大文字）
  AND type = 'PROCEDURE'       -- PROCEDURE / FUNCTION / PACKAGE / PACKAGE BODY / TRIGGER
ORDER BY line;

-- 特定のキーワードを含むオブジェクトを検索する（コードの全文検索）
SELECT DISTINCT name, type
FROM USER_SOURCE
WHERE UPPER(text) LIKE '%EMPLOYEES%'   -- EMPLOYEES を参照しているオブジェクトを探す
ORDER BY type, name;

-- オブジェクトの依存関係を確認する（このオブジェクトが何に依存しているか）
SELECT
    name              AS object_name,
    type              AS object_type,
    referenced_name   AS depends_on,
    referenced_type   AS dep_type,
    referenced_owner  AS dep_owner
FROM USER_DEPENDENCIES
WHERE name = 'EMP_SUMMARY_V'   -- 調査対象のビュー名
ORDER BY referenced_type, referenced_name;

-- 逆方向: このテーブルに依存しているオブジェクトを確認する
SELECT
    name              AS dependent_object,
    type              AS dependent_type
FROM USER_DEPENDENCIES
WHERE referenced_name  = 'EMPLOYEES'
  AND referenced_type  = 'TABLE'
ORDER BY type, name;

-- セグメント（テーブル・インデックス）のサイズを確認する
SELECT
    segment_name,
    segment_type,
    tablespace_name,
    bytes / 1024 / 1024 AS size_mb,
    blocks,
    extents
FROM USER_SEGMENTS
ORDER BY bytes DESC;

-- スキーマ単位の使用スペースを集計する（DBA権限）
SELECT
    owner,
    SUM(bytes) / 1024 / 1024 AS total_mb
FROM DBA_SEGMENTS
GROUP BY owner
ORDER BY total_mb DESC;

-- 表領域の空き容量を確認する（DBA権限）
SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1024 / 1024, 1) AS free_mb
FROM DBA_FREE_SPACE
GROUP BY tablespace_name
ORDER BY free_mb;

-- 表領域全体のサイズと使用率を確認する（DBA権限）
SELECT
    d.tablespace_name,
    ROUND(d.total_mb, 1)                       AS total_mb,
    ROUND(d.total_mb - NVL(f.free_mb, 0), 1)  AS used_mb,
    ROUND(NVL(f.free_mb, 0), 1)               AS free_mb,
    ROUND((d.total_mb - NVL(f.free_mb, 0)) / d.total_mb * 100, 1) AS pct_used
FROM (
    SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb
    FROM DBA_DATA_FILES GROUP BY tablespace_name
) d
LEFT JOIN (
    SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb
    FROM DBA_FREE_SPACE GROUP BY tablespace_name
) f ON d.tablespace_name = f.tablespace_name
ORDER BY pct_used DESC;

-- V$SESSION: 現在のセッション情報
SELECT sid, serial#, username, status, sql_id FROM V$SESSION WHERE username IS NOT NULL;

-- V$SQL: ライブラリキャッシュ上の SQL 情報
SELECT sql_text, executions, elapsed_time/1000000 AS elapsed_sec
FROM V$SQL WHERE executions > 0 ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

-- V$LOCK: 現在保持・待機中のロック情報
SELECT l.sid, l.type, l.mode_held, l.mode_requested FROM V$LOCK l WHERE l.block = 1;

-- ① 最近作成・変更されたオブジェクトを確認する
SELECT object_name, object_type, last_ddl_time
FROM USER_OBJECTS
WHERE last_ddl_time >= SYSDATE - 7   -- 直近1週間
ORDER BY last_ddl_time DESC;

-- ② テーブルのすべての列をコンマ区切りで取得する（SELECT 文の作成に便利）
SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) AS column_list
FROM USER_TAB_COLUMNS
WHERE table_name = 'EMPLOYEES';

-- ③ 外部キーがあるのに参照先にインデックスがないテーブルを検出する
SELECT
    a.table_name    AS child_table,
    a.column_name   AS fk_column
FROM USER_CONS_COLUMNS a
JOIN USER_CONSTRAINTS c ON a.constraint_name = c.constraint_name
WHERE c.constraint_type = 'R'   -- 外部キー
  AND NOT EXISTS (
      SELECT 1 FROM USER_IND_COLUMNS i
      WHERE i.table_name  = a.table_name
        AND i.column_name = a.column_name
        AND i.column_position = 1
  );
-- → パフォーマンス問題の原因になりやすい（外部キー列にはインデックスを付ける）

-- ④ 統計が古いテーブルを検出する（最終統計収集が30日以上前）
SELECT table_name, num_rows, last_analyzed
FROM USER_TABLES
WHERE last_analyzed < SYSDATE - 30
   OR last_analyzed IS NULL
ORDER BY last_analyzed NULLS FIRST;

-- ⑤ DDL を取得する（DBMS_METADATA）
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX', 'EMP_PK') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('VIEW',  'HR_DEPT60_V') FROM DUAL;