テーブル設計確認系SQL（Oracle）

概要

• 記載内容
  • テーブル、パーティション、インデックスなどテーブル設計を調査するためのSQLです。
• 背景、環境情報
  • 本当はドキュメントでまとめておいてほしいけど、無かったりメンテされてないのでほぼ毎回調査することになるので、ナレッジ化しておこうかと。
  • 環境はOracleです。
• 対象読者
  • 自分、ドキュメントがないなどの理由でDB直接調べるしかない人

テーブル設計

テーブル列定義

テーブルやビューの列定義を表示したり、ファンクションまたはプロシージャの仕様を表示する。

DESC テーブル名

テーブル・カラム一覧

USER_TABLESは現行のユーザーが所有するリレーショナルのみ表示されるので、全部見る場合はDBA_TABLESを参照

SELECT
    UT.TABLE_NAME
    , UTCM.COMMENTS AS TAB_COMMENTS
    , UTC.COLUMN_NAME
    , UCC.COMMENTS AS COL_COMMENTS
    , UTC.DATA_LENGTH
    , UTC.DATA_TYPE AS
    , CASE 
        WHEN PK.COLUMN_NAME IS NULL 
            THEN '' 
        ELSE 'PK' 
        END AS PK 
FROM
    USER_TABLES UT 
    LEFT JOIN USER_TAB_COMMENTS UTCM 
        ON (UTCM.TABLE_NAME = UT.TABLE_NAME) 
    LEFT JOIN USER_TAB_COLUMNS UTC 
        ON (UTC.TABLE_NAME = UT.TABLE_NAME) 
    LEFT JOIN USER_COL_COMMENTS UCC 
        ON ( 
            UCC.TABLE_NAME = UT.TABLE_NAME 
            AND UCC.COLUMN_NAME = UTC.COLUMN_NAME
        ) 
    LEFT JOIN ( 
        SELECT
            S1.TABLE_NAME
            , S1.COLUMN_NAME 
        FROM
            USER_CONS_COLUMNS S1 
        WHERE
            S1.CONSTRAINT_NAME IN ( 
                SELECT
                    S2.CONSTRAINT_NAME 
                FROM
                    USER_CONSTRAINTS S2 
                WHERE
                    S2.TABLE_NAME IN ( 
                        SELECT
                            S3.TABLE_NAME 
                        FROM
                            USERTABLES S3 
                        WHERE
                            S3.STATUS = 'VALID'
                    ) 
                    AND S2.CONSTRAINT_TYPE = 'P'
                    -- P : 主キー,U : 一意キー,R : 外部キー,C : CHECK、NOT NULL
            )
    ) PK 
        ON ( 
            PK.TABLE_NAME = UTC.BLE_NAME 
            AND PK.COLUMN_NAME = UTC.COLUMN_NAME
        ) 
WHERE
    UT.TABLE_NAME = 'テーブル名';  -- これ外せば全テーブル出力となる

インデックス定義

インデックス一覧

SELECT
    TABLE_OWNER
    , TABLE_NAME
    , TABLE_TYPE
    , INDEX_NAME
    , INDEX_TYPE -- INVALID（無効）は再作成必要。N/Aはパーティション化されているため
    , PARTITIONED
    , STATUS
    , NUM_ROWS
    , SAMPLE_SIZE 
FROM
    ALL_INDEXES 
WHERE
    TABLE_NAME = 'テーブル名'                   -- 削除で全テーブル対象となる
ORDER BY
    TABLE_OWNER
    , TABLE_NAME
    , INDEX_NAME

パーティション定義

パーティションテーブル一覧

SELECT
    OWNER
    , TABLE_NAME 
FROM
    ALL_TABLES 
WHERE
    PARTITIONED = 'YES'

パーティション一覧

SELECT
    TABLE_OWNER
    , TABLE_NAME
    , COMPOSITE
    , PARTITION_NAME
    , HIGH_VALUE
    , PARTITION_POSITION
    , COMPRESSION
    , NUM_ROWS 
FROM
    ALL_TAB_PARTITIONS 
WHERE
    TABLE_NAME = 'テーブル名' 
ORDER BY
    TABLE_NAME
    , PARTITION_NAME

パーティションキー確認

SELECT
    OWNER
    , NAME
    , OBJECT_TYPE
    , SUBSTR(COLUMN_NAME,1,30)
    , COLUMN_POSITION
FROM
    ALL_PART_KEY_COLUMNS
WHERE
    NAME = 'パーティション名' 

PL/SQL関連

PL/SQL一覧

PL/SQL大好きシステムでは結構な数作成されているのでまずは件数チェックしたほうが良い

SELECT COUNT(1) FROM ALL_SOURCE;
SELECT COUNT(1) FROM ALL_SOURCE GROUP BY NAME ORDER BY NAME;

PL/SQL定義

定義と実処理の二種類確認しておく。

-- 定義
SELECT ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE' AND OBJECT_NAME = 'パッケージ名'
-- 実処理
SELECT ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE BODY' AND OBJECT_NAME = 'パッケージ名'

DBリンク

DBリンク名の確認

select * from DBA_DB_LINKS;

DBリンク接続確認

select * from テーブル名@DBリンク名;