概要
- Oracle Database 12c のテーブル定義 (テーブル、カラム、インデックス、制約) を確認する
- 定義情報は静的データ・ディクショナリ・ビューで確認する
今回の環境
- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
- SQL*Plus: Release 12.2.0.1.0
テーブル定義は静的データ・ディクショナリ・ビューで確認する
テーブル定義などの情報はデータ・ディクショナリに収められている。
データ・ディクショナリはOracle Databaseの重要な部分であり、データベースに関する管理メタデータを提供する読取り専用の表の集合です。
データ・ディクショナリには次のような情報が含まれています。
・列のデフォルト値、整合性制約情報など、データベース内の各スキーマ・オブジェクトの定義
・スキーマ・オブジェクトに割り当てられている領域、およびスキーマ・オブジェクトによって現在使用されている領域の容量
・Oracle Databaseユーザーの名前、ユーザーに付与された権限とロール、およびユーザーに関連する監査情報
参照できるビューはそれぞれの情報に対して ALL_, DBA_, USER_ の3種類。
多くのデータ・ディクショナリ表には、対応するビューが3種類あります。
・ALL_ビューには、カレント・ユーザーのスキーマの情報など、カレント・ユーザーからアクセス可能な情報がすべて表示されます。ユーザーが他のスキーマのオブジェクトにアクセスする権限またはロールを付与されている場合には、それらのオブジェクトの情報も表示されます。
・DBA_ビューには、データベース全体の関連情報がすべて表示されます。DBA_ビューは、管理者のみが使用するように設計されています。SYSDBAシステム権限またはSELECT ANY DICTIONARY権限、またはSELECT_CATALOG_ROLEロールを持つユーザー、または権限を直接付与されたユーザーのみが、これらを問い合せることができます。SELECT ANY DICTIONARY権限は、システムの初回インストールの際にDBAロールに割り当てられます。
・USER_ビューには、カレント・ユーザーのスキーマの情報がすべて表示されます。これらのビューの問合せを行うために特別な権限は必要ありません。
1つのデータ・ディクショナリ表に対応するALL_、DBA_およびUSER_ビューの列は、通常ほぼ同一です。
テーブル
ALL_TABLES
ALL_TABLESは、現行のユーザーがアクセスできるリレーショナル表を示します。このビューの統計情報を収集するには、DBMS_STATSパッケージを使用します。
DBA_TABLESは、データベース内のリレーショナル表をすべて示します。
USER_TABLESは、現行のユーザーが所有するリレーショナル表を示します。このビューは、OWNER列を表示しません。
テーブル一覧を出力する例。
SQL> SELECT TABLE_NAME, TABLESPACE_NAME, PARTITIONED FROM ALL_TABLES WHERE OWNER = 'FOO_USER';
TABLE_NAME TABLESPACE_NAME PARTITIONED
--------------- ------------------ -----------
FOO_TABLE USERS NO
TEST USERS NO
MY_TABLE USERS NO
FOO_NAMES USERS NO
カラム
ALL_TAB_COLUMNS
ALL_TAB_COLUMNSは、現行のユーザーがアクセスできる表、ビューおよびクラスタの列を示します。
DBA_TAB_COLUMNSは、データベース内すべての表、ビューおよびクラスタの列を示します。
USER_TAB_COLUMNSは、現行のユーザーが所有する表、ビューおよびクラスタの列を示します。このビューは、OWNER列を表示しません。
テーブルのカラム一覧を出力する例。
SQL> SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'HOGE';
COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE DATA_DEFAULT
-------------------------------------------------------------------------------------------
HOGE_ID NUMBER 22 N "FOO_USER"."ISEQ$$_73470".nextval
CODE CHAR 16 N
TYPE CHAR 32 N 'AAAAZZZZ'
TEXT VARCHAR2 64 Y
DATA BINARY_FLOAT 4 Y
UPDATED_AT TIMESTAMP(6) 11 Y
DESCRIBE
指定した表、ビューまたはシノニムの列定義を表示したり、指定したファンクションまたはプロシージャの仕様を表示します。
テーブルのカラム一覧を出力する例。
SQL> DESC HOGE;
名前 NULL? 型
------------------ ---------- --------------------
HOGE_ID NOT NULL NUMBER
CODE NOT NULL CHAR(4 CHAR)
TYPE NOT NULL CHAR(8 CHAR)
TEXT VARCHAR2(16 CHAR)
DATA BINARY_FLOAT
UPDATED_AT TIMESTAMP(6)
インデックス
ALL_IND_COLUMNS
ALL_IND_COLUMNSは、現行のユーザーがアクセスできるすべての表上の索引の列をすべて示します。
DBA_IND_COLUMNSは、データベース内のすべての表の索引の列をすべて示します。
USER_IND_COLUMNSは、現行のユーザーが所有する索引の列、および現行のユーザーが所有する表の索引の列を示します。このビューは、INDEX_OWNERおよびTABLE_OWNER列を表示しません。
テーブルのインデックス一覧を出力する例。
SQL> SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'HOGE';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
-----------------------------------------------------------
HOGE_PRIMARY_KEY HOGE_ID 1
FOO_UNIQUE_INDEX CODE 1
BAR_BITMAP_INDEX TYPE 1
BAZ_MULTIPLE_COLUMN_INDEX HOGE_ID 1
BAZ_MULTIPLE_COLUMN_INDEX TYPE 2
ALL_INDEXES
ALL_INDEXESは、現行のユーザーがアクセスできる表の索引を示します。
DBA_INDEXESは、データベース内の索引をすべて示します。
USER_INDEXESは、現行のユーザーが所有する索引を示します。このビューは、OWNER列を表示しません。
テーブルのインデックス一覧を出力する例。
SQL> SELECT INDEX_NAME, INDEX_TYPE FROM ALL_INDEXES WHERE TABLE_NAME = 'HOGE';
INDEX_NAME INDEX_TYPE
---------------------------- ----------
HOGE_PRIMARY_KEY NORMAL
FOO_UNIQUE_INDEX NORMAL
BAR_BITMAP_INDEX BITMAP
BAZ_MULTIPLE_COLUMN_INDEX NORMAL
制約
ALL_CONS_COLUMNS
ALL_CONS_COLUMNSは、現行のユーザーがアクセスでき、また制約に指定されている列を示します。
DBA_CONS_COLUMNSは、制約に指定されているデータベース内の列をすべて示します。
USER_CONS_COLUMNSは、現行のユーザーが所有していて、制約に指定されている列を示します。
テーブルの制約一覧を出力する例。
SQL> SELECT CONSTRAINT_NAME, COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE TABLE_NAME = 'HOGE';
CONSTRAINT_NAME COLUMN_NAME
------------------- -----------
SYS_C007466 HOGE_ID
SYS_C007467 CODE
SYS_C007468 TYPE
HOGE_PRIMARY_KEY HOGE_ID
ALL_CONSTRAINTS
ALL_CONSTRAINTSは、現行のユーザーがアクセスできる表の制約定義を示します。
DBA_CONSTRAINTSは、データベース内の制約定義をすべて示します。
USER_CONSTRAINTSは、現行のユーザー・スキーマ内の表の制約定義を示します。
テーブルの制約一覧を出力する例。
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'HOGE';
CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------- ------------------ ----------------
SYS_C007466 C
SYS_C007467 C
SYS_C007468 C
HOGE_PRIMARY_KEY P HOGE_PRIMARY_KEY