概要
Oracleを利用するシステム開発において、DBオブジェクトの一覧や詳細が記載されたドキュメントが
ないことがたまにある。また開発途中だと同期化されていないこともある
Oracleに登録されているDBオブジェクトとドキュメントが同期化されていることが望ましいが、
どこの現場でも利用できるよう、最新のDBオブジェクトの内容をSQLでOracleから抽出して作成する。
作成したSQL
- DBオブジェクト一覧
- シーケンス一覧
- テーブル一覧
- トリガー一覧
- テーブルエンティティ
- ストアドソース
DBオブジェクト一覧
SELECT T1.OWNER "スキーマ"
,T1.OBJECT_NAME "オブジェクト名"
,T1.OBJECT_TYPE "種類"
,TO_CHAR(T1.CREATED, 'YYYY/MM/DD HH24:MI:SS') "作成日"
,TO_CHAR(T1.LAST_DDL_TIME, 'YYYY/MM/DD HH24:MI:SS') "最終更新日"
FROM ALL_OBJECTS T1
WHERE T1.OWNER NOT LIKE '%SYS%'
ORDER BY 1,2;
＜実行結果＞
| スキーマ | オブジェクト名 | 種類 | 作成日 | 最終更新日 |
|---|---|---|---|---|
| HOGE | IDX_CUSTMER | INDEX | 2020/02/07 13:39:23 | 2020/02/07 14:05:34 |
| HOGE | PGL_CUSTMER | TABLE | 2020/02/07 13:39:23 | 2020/02/07 14:05:34 |
| HOGE | SEQ_HOGE | SEQUENCE | 2020/02/07 13:39:23 | 2020/02/07 14:05:34 |
| HOGE | VW_CUSTMER | VIEW | 2020/02/07 13:39:23 | 2020/02/07 14:05:34 |
シーケンス一覧
SELECT SEQUENCE_OWNER "スキーマ"
,SEQUENCE_NAME "シーケンス名"
,MIN_VALUE "最小値"
,MAX_VALUE "最大値"
,INCREMENT_BY "増分値"
,LAST_NUMBER "前回シーケンス値"
FROM ALL_SEQUENCES
ORDER BY 1;
＜実行結果＞
| スキーマ | シーケンス名 | 最小値 | 最大値 | 増分値 | 前回シーケンス値 |
|---|---|---|---|---|---|
| HOGE | SEQ_HOGE | 1 | 99999 | 1 | 123 |
テーブル一覧
SELECT T1.OWNER "スキーマ"
,T1.TABLE_NAME "物理テーブル名"
,T1.COMMENTS "論理テーブル名"
FROM ALL_TAB_COMMENTS T1
WHERE T1.OWNER NOT LIKE '%SYS%' -- SYS関連スキーマを除く
ORDER BY 1,2;
＜実行結果＞
| スキーマ | 物理テーブル名 | 論理テーブル名 |
|---|---|---|
| HOGE | TBL_CUSTMER | 会員テーブル |
トリガー一覧
SELECT T1.OWNER "スキーマ"
,T1.TRIGGER_NAME "トリガー名"
,T1.TRIGGER_TYPE "トリガータイプ"
,T1.TRIGGERING_EVENT "イベント"
,T1.TABLE_NAME "テーブル名"
,T1.STATUS "ステータス"
,T1.ACTION_TYPE "実行形式"
,T1.CROSSEDITION "CROSSEDITION形式"
FROM ALL_TRIGGERS T1
ORDER BY 1,2;
＜実行結果＞
| スキーマ | トリガー名 | トリガータイプ | イベント | テーブル名 | ステータス | 実行形式 | CROSSEDITION形式 |
|---|---|---|---|---|---|---|---|
| HOGE | TRG_CUSTMERKBN | BEFORE EACH ROW | INSERT OR UPDATE | TBL_CUSTMERKBN | ENABLED | PL/SQL | NO |
テーブルエンティティ
SELECT T1.OWNER "スキーマ"
,T1.TABLE_NAME "テーブル名"
,T1.COLUMN_ID "NO"
,T1.COLUMN_NAME "物理カラム名"
,T2.COMMENTS "論理カラム名"
,NVL2(V1.COLUMN_NAME, V1.COLUMN_POSITION, NULL) "主キー"
,T1.DATA_TYPE "型"
,DECODE(
T1.DATA_TYPE, 'NUMBER', '(' || T1.DATA_PRECISION || ',' || T1.DATA_SCALE || ')',
'VARCHAR2', '(' || T1.DATA_LENGTH || ')',
NULL
) "桁数"
,T1.NULLABLE "NULL許可"
FROM ALL_TAB_COLS T1
,ALL_COL_COMMENTS T2,
(SELECT I2.OWNER
,I2.TABLE_NAME
,I2.COLUMN_NAME
,I2.COLUMN_POSITION
FROM ALL_CONSTRAINTS I1
,ALL_IND_COLUMNS I2
WHERE I1.OWNER = I2.OWNER
AND I1.TABLE_NAME = I2.TABLE_NAME
AND I1.INDEX_NAME = I2.INDEX_NAME
AND I1.TABLE_OWNER NOT LIKE '%SYS%'
AND (:TABLENAME IS NULL OR I1.TABLE_NAME = UPPER(:TABLENAME))
AND I1.CONSTRAINT_TYPE = 'P'
) V1
WHERE T1.OWNER = T2.OWNER
AND T1.OWNER NOT LIKE '%SYS%'
AND (:TABLENAME IS NULL OR T1.TABLE_NAME = UPPER(:TABLENAME))
AND T1.TABLE_NAME = T2.TABLE_NAME
AND T1.COLUMN_NAME = T2.COLUMN_NAME
AND T2.OWNER = V1.OWNER (+)
AND T2.TABLE_NAME = V1.TABLE_NAME (+)
AND T2.COLUMN_NAME = V1.COLUMN_NAME (+)
ORDER BY T1.OWNER, T1.TABLE_NAME, T1.COLUMN_ID;
＜実行結果＞
| スキーマ | テーブル名 | NO | 物理カラム名 | 論理カラム名 | 主キー | 型 | 桁数 | NULL許可 |
|---|---|---|---|---|---|---|---|---|
| HOGE | TBL_CUSTMERKBN | 1 | CUSTMERKBN | 会員区分 | 1 | NUMBER | (1,0) | N |
| HOGE | TBL_CUSTMERKBN | 2 | CUSTMERKBNMEI | 会員区分名称 | VARCHAR2 | (256) | N | |
| HOGE | TBL_CUSTMERKBN | 3 | REGDATE | 更新日時 | DATE | Y | ||
| HOGE | TBL_CUSTMERKBN | 4 | REGSTAFFID | 更新職員ID | VARCHAR2 | (32) | Y | |
| HOGE | TBL_CUSTMERKBN | 5 | VERSION | バージョン | NUMBER | (12,0) | N |
ストアドソース
ストアドパッケージの場合
SELECT T1.TYPE "タイプ"
,T1.LINE "行番号"
,T1.TEXT "ソース"
FROM ALL_SOURCE T1
WHERE T1.OWNER NOT LIKE '%SYS%'
AND T1.NAME = UPPER(:NAME)
AND T1.TYPE IN ('PACKAGE BODY', 'PACKAGE')
ORDER BY T1.TYPE, T1.LINE;
ストアドパッケージ以外の場合
SELECT T1.LINE "行番号"
,T1.TEXT "ソース"
FROM ALL_SOURCE T1
WHERE T1.OWNER NOT LIKE '%SYS%'
AND T1.NAME = UPPER(:NAME)
ORDER BY T1.LINE;
＜実行結果＞
| タイプ | 行番号 | ソース |
|---|---|---|
| PACKAGE | 1 | PACKAGE "PKG_TEST" IS |
| PACKAGE | 2 | |
| PACKAGE BODY | 1 | PACKAGE BODY "PKG_TEST" IS |
| PACKAGE BODY | 2 |
まとめ・感想
- ストアド系のソースは開発途中や出荷準備でも利用できるため作成しておいてよかった。
- テーブル一覧にはエクステント等の情報があった方がよい。
- 当初は自スキーマを抽出対象としており、USER_XXビューを参照していたが、システムによっては複数のスキーマを使用することがあるため、ALL_XXビューから抽出するようにした。ただし、SYS系はDBオブジェクトは不要なので対象外とした。