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は管理者権限が必要です。
以上がデータディクショナリの紹介となります。