概要

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オブジェクトは不要なので対象外とした。