トップ > SQL入門 > 38章
36章 データディクショナリ
それでは今回はデータディクショナリについて学んでいきましょう。

データディクショナリとはデータベースの管理情報であり、
そのデータディクショナリは管理者(SYS)の所有物です。

そのデータディクショナリの情報は我々一般ユーザでも参照することができます。
ではどのような時にデータディクショナリを使用するのでしょうか。

例えば自分が所有している表の一覧を知りたい。
今まで定義した制約を一覧で確認したい。
索引やビューなどすべてのオブジェクト情報が知りたい。
すべてデータディクショナリを使用すれば確認することができます。

自分が所有するテーブル情報


[構文] 所有するテーブル情報
 SELECT TABLE_NAME
 FROM USER_TABLES;

上記SQLを使用すると自分が所有する表の一覧が確認できます。
このUSER_TABLESというのがデータディクショナリとなります。

  SQL> SELECT TABLE_NAME FROM USER_TABLES;

  TABLE_NAME
  ------------------------------------
  LOCATIONS
  DEPARTMENTS
  EMPLOYEES
  GRADES
  

データディクショナリのUSER_TABLESはその他の情報も取得できます。

  SQL> desc user_tables
   Name                            Null?    Type
   ------------------------------- -------- ------------------------
   TABLE_NAME                      NOT NULL VARCHAR2(128)
   TABLESPACE_NAME                          VARCHAR2(30)
   CLUSTER_NAME                             VARCHAR2(128)
   IOT_NAME                                 VARCHAR2(128)
   STATUS                                   VARCHAR2(8)
   PCT_FREE                                 NUMBER
   PCT_USED                                 NUMBER
   INI_TRANS                                NUMBER
   MAX_TRANS                                NUMBER
   INITIAL_EXTENT                           NUMBER
   NEXT_EXTENT                              NUMBER
   MIN_EXTENTS                              NUMBER
   MAX_EXTENTS                              NUMBER
   PCT_INCREASE                             NUMBER
   FREELISTS                                NUMBER
   FREELIST_GROUPS                          NUMBER
   LOGGING                                  VARCHAR2(3)
   BACKED_UP                                VARCHAR2(1)
   NUM_ROWS                                 NUMBER
   BLOCKS                                   NUMBER
   EMPTY_BLOCKS                             NUMBER
   AVG_SPACE                                NUMBER
   CHAIN_CNT                                NUMBER
   AVG_ROW_LEN                              NUMBER
   AVG_SPACE_FREELIST_BLOCKS                NUMBER
   NUM_FREELIST_BLOCKS                      NUMBER
   DEGREE                                   VARCHAR2(10)
   INSTANCES                                VARCHAR2(10)
   CACHE                                    VARCHAR2(5)
   TABLE_LOCK                               VARCHAR2(8)
   SAMPLE_SIZE                              NUMBER
   LAST_ANALYZED                            DATE
   PARTITIONED                              VARCHAR2(3)
   IOT_TYPE                                 VARCHAR2(12)
   TEMPORARY                                VARCHAR2(1)
   SECONDARY                                VARCHAR2(1)
   NESTED                                   VARCHAR2(3)
   BUFFER_POOL                              VARCHAR2(7)
   FLASH_CACHE                              VARCHAR2(7)
   CELL_FLASH_CACHE                         VARCHAR2(7)
   ROW_MOVEMENT                             VARCHAR2(8)
   GLOBAL_STATS                             VARCHAR2(3)
   USER_STATS                               VARCHAR2(3)
   DURATION                                 VARCHAR2(15)
   SKIP_CORRUPT                             VARCHAR2(8)
   MONITORING                               VARCHAR2(3)
   CLUSTER_OWNER                            VARCHAR2(128)
   DEPENDENCIES                             VARCHAR2(8)
   COMPRESSION                              VARCHAR2(8)
   COMPRESS_FOR                             VARCHAR2(30)
   DROPPED                                  VARCHAR2(3)
   READ_ONLY                                VARCHAR2(3)
   SEGMENT_CREATED                          VARCHAR2(3)
   RESULT_CACHE                             VARCHAR2(7)
   CLUSTERING                               VARCHAR2(3)
   ACTIVITY_TRACKING                        VARCHAR2(23)
   DML_TIMESTAMP                            VARCHAR2(25)
   HAS_IDENTITY                             VARCHAR2(3)
   CONTAINER_DATA                           VARCHAR2(3)
   INMEMORY                                 VARCHAR2(8)
   INMEMORY_PRIORITY                        VARCHAR2(8)
   INMEMORY_DISTRIBUTE                      VARCHAR2(15)
   INMEMORY_COMPRESSION                     VARCHAR2(17)
   INMEMORY_DUPLICATE                       VARCHAR2(13)
   

上記データディクショナリは様々な情報を取得できますが、
現状のところはテーブル名だけ取得できる列名(TABLE_NAME)
だけ抑えておけばよいかと思います。

自分が所有するテーブルの列情報


[構文] 所有するテーブルの列情報
 SELECT COLUMN_NAME, DATA_TYPE,
        DATA_LENGTH, DATA_DEFAULT
 FROM USER_TAB_COLUMNS;

COLUMN_NAME列は列名、DATA_DEFAULT列はデフォルト値を確認することができます。

  SQL> SELECT column_name,data_type,data_length,data_default
    2  FROM user_tab_columns
    3  WHERE table_name = 'EMPLOYEES';

  COLUMN_NAME     DATA_TYPE  DATA_LENGTH DATA_DEFAU
  --------------- ---------- ----------- ----------
  DEPT_ID         NUMBER              22
  MANAGER_ID      NUMBER              22
  HIRE_DATE       DATE                 7
  SALARY          NUMBER              22
  TEL             VARCHAR2            12
  ADDRESS         VARCHAR2           100
  LAST_NAME       VARCHAR2            30
  FIRST_NAME      VARCHAR2            30
  EMP_ID          NUMBER              22

  9 rows selected.
  

上記例のようにWHERE句を使用して、取得したいテーブルだけを指定することも可能です。

制約の情報


[構文] 制約の情報
 SELECT TABLE_NAME,CONSTRAINT_NAME,
       CONSTRAINT_TYPE,SEARCH_CONDITION
 FROM USER_CONSTRAINTS;

  SQL> SELECT table_name,constraint_name,constraint_type,search_condition
    2  FROM user_constraints;

  TABLE_NAME      CONSTRAINT_NAME      C SEARC
  --------------- -------------------- - -----
  EMPLOYEES       EMP_DEPT_ID_FK       R
  DEPARTMENTS     DEPT_LOC_ID_FK       R
  LOCATIONS       LOC_LOC_ID_PK        P
  DEPARTMENTS     DEPT_DEPT_ID_PK      P
  EMPLOYEES       EMP_EMP_ID_PK        P

  9 rows selected.
  

CONSTRAINT_TYPEの項目で制約タイプが確認できます。
P:主キー制約
U:一意キー制約
R:外部キー制約
C:チェック制約 or NOT NULL

Cはチェック制約もしくはNOT NULLとなりますが、
SEARCH_CONDITION列で「列名 IS NOT NULL」と表示されば、NOT NULL制約となります。

またこのUSER_CONSTRAINTは制約名と制約タイプはわかりますが、
どの列に定義されているかはわかりません。
どの列に制約を定義しているのかを確認したい場合は、USER_CONS_COLUMNSを使用します。

[構文] 制約の列情報
 SELECT CONSTRAINT_NAME,TABLE_NAME,
       COLUMN_NAME,POSITION
 FROM USER_CONS_COLUMNS;

  SQL> SELECT constraint_name,table_name,column_name,position
    2  FROM user_cons_columns;

  CONSTRAINT_NAME      TABLE_NAME      COLUMN_NAME       POSITION
  -------------------- --------------- --------------- ----------
  LOC_LOC_ID_PK        LOCATIONS       LOC_ID                   1
  DEPT_DEPT_ID_PK      DEPARTMENTS     DEPT_ID                  1
  DEPT_LOC_ID_FK       DEPARTMENTS     LOC_ID                   1
  EMP_EMP_ID_PK        EMPLOYEES       EMP_ID                   1
  EMP_DEPT_ID_FK       EMPLOYEES       DEPT_ID                  1
  

POSITIONは複合制約の場合、どの列の順番で制約が定義されているかを確認できる列です。

その他にもたくさんデータディクショナリがあり、いろいろな情報を調べることができます。

その他のデータディクショナリ一覧
USER_INDEXES 索引名、索引タイプの情報
USER_IND_COLUMNS 索引名、索引が定義されている列
USER_VIEWS ビュー情報
USER_SEQUENCES 順序の情報
USER_SYNONYMS シノニム情報

上記のデータディクショナリを使用して、順序や索引などの情報も確認することができます。
環境がある方は試してみるとよいかと思います。

データディクショナリの接頭辞


USER_TABLESやUSER_CONSTRAINTSなどのデータディクショナリの接頭辞は
USERですが、こちらは変更できます。

その他のデータディクショナリ一覧
USER 自分が所有するオブジェクトの一覧
ALL 自分が所有できるオブジェクトと所有していなくてもアクセス権限があるオブジェクトの一覧
DBA DB上に存在するすべてのオブジェクト一覧

上記の接頭辞を変更し、情報を確認することができます。
ただし、DBAは管理者権限が必要です。


以上がデータディクショナリの紹介となります。