データ・ディクショナリ

Oracle テーブル一覧

2022.07.23

DBA_TABLES テーブル一覧

基本的にはDBA_TABLESを参照すれば良いです。注意点としてそのまま一覧出力した場合、マテリアライズド・ビュー（マテビュー本体に加え、マテビュー用ログテーブルも）が含まれる点です。またテーブル毎の表領域を調べたい場合、パーティションテーブルについてはDBA_TAB_PARTITIONSと結合しないと取得できません。そのためテーブル一覧といえど要求によっていろいろ求め方が変わってきます。以下４パターン用意してみました。

No.一覧名表示条件
1一般テーブルマテビュー、マテビューログテーブル、パーティションテーブルを除く一覧。
2テーブル（パーティションテーブル含む）マテビュー、マテビューログテーブルを除く一覧。パーティションテーブルについては、パーティション毎のサイズ合計、代表(max値)パーティションの表領域を出力
3パーティションパーティション毎の一覧
4全てのテーブルマテビュー、マテビューログテーブル、パーティションテーブルを含む全てのテーブル一覧。（パーティションテーブルについてはパーティション毎のサイズ合計、代表(max値)パーティションの表領域を出力

DBA_TABLESを参照する際に注意する事

  • DBA_TABLES単体では通常のテーブルとマテビューの区別がつかない
  • マテビューログテーブルも含まれるため求める
  • マテビュー、マテビューログを除くテーブル一覧を求める場合DBA_MVIEWS、DBA_MVIEW_LOGSと結合する必要がある。
  • パーティションテーブルの表領域は保持しないため求める場合DBA_TAB_PARTITIONSをさらに結合する必要がある。

一般テーブル一覧

一般テーブル一覧出力SQL

マテビュー、マテビューログテーブル、パーティションテーブルを除くテーブル一覧を出力します。以下は’DABADA’スキーマに絞っていますが、適宜変更して使ってください。

select t.OWNER,t.TABLE_NAME,c.COMMENTS,t.LOGGING
,t.TEMPORARY
,s.BYTES/1024/1024 M_BYTE  -- サイズ
from DBA_TABLES t
left outer join DBA_TAB_COMMENTS c
on t.OWNER = c.OWNER  and t.TABLE_NAME = c.TABLE_NAME and c.TABLE_TYPE ='TABLE'
left outer join DBA_SEGMENTS s
on t.OWNER = s.OWNER and t.TABLE_NAME = s.SEGMENT_NAME and s.SEGMENT_TYPE = 'TABLE'
where t.OWNER='DABADA'
and not exists -- マテビューを除く
(select 'X' from DBA_MVIEWS v where t.OWNER = v.OWNER and t.TABLE_NAME = v.MVIEW_NAME)
and not exists -- マテビューログテーブルを除く
(select 'X' from DBA_MVIEW_LOGS l where t.OWNER = l.LOG_OWNER and t.TABLE_NAME = l.LOG_TABLE)
and t.PARTITIONED='NO' -- パーティションテーブルを除く
order by t.OWNER,t.TABLE_NAME
;
一般テーブル一覧出力例

サイズが表示されていないテーブルはCREATE TABLE後一度もセグメント作成（エクステント割り当て）が行われていない（遅延セグメント作成と呼ばれる機能によるもの）ためです。

                                                           LOGG TEMPO
OWNER    TABLE_NAME       COMMENTS                         ING  RARY      M_BYTE
-------- ---------------- -------------------------------- ---- ----- ----------
DABADA   OY_PREFECTURE    都道府県マスタ                   NO   N          .0625
DABADA   OY_REGION        地域マスタ                       NO   N          .0625
DABADA   TABINF           テーブル情報                     NO   Y
DABADA   TBL26            テーブル２６                     NO   N            176
DABADA   TBL29            一般テーブルサンプルデータ無     NO   N
DABADA   TBL304           一般テーブルサンプル304          YES  N
DABADA   TEMP100          一時表サンプル                   NO   Y

7行が選択されました。

SQL>
SQL*Plus表示用
set lin 300
set pages 100
COLUMN OWNER  FORMAT A8
COLUMN TABLE_NAME FORMAT A16
COLUMN COMMENTS   FORMAT A32
COLUMN TABLESPACE_NAME  FORMAT A12
COLUMN TEMPORARY FORMAT A5
COLUMN TEMPORARY HEADING 'TEMPO|RARY'
COLUMN LOGGING FORMAT A4
COLUMN LOGGING HEADING 'LOGG|ING'

テーブル一覧（パーティションテーブル含む）

テーブル一覧出力（パーティションテーブル含む）SQL

マテビュー、マテビューログテーブル、パーティションテーブルを除くテーブル一覧を出力します。以下は’DABADA’スキーマに絞っていますが、適宜変更して使ってください。

select t.OWNER,t.TABLE_NAME,c.COMMENTS
,decode(t.PARTITIONED,'YES',p.LOGGING,t.LOGGING) LOGGING
,t.TEMPORARY,t.PARTITIONED,b.PARTITIONING_TYPE,b.SUBPARTITIONING_TYPE
,decode(t.PARTITIONED,'YES',p.M_BYTE,s.BYTES/1024/1024) M_BYTE
from dba_tables t
left outer join dba_tab_comments c
on t.OWNER = c.OWNER  and t.TABLE_NAME = c.TABLE_NAME and c.TABLE_TYPE ='TABLE'
left outer join dba_segments s
on t.OWNER = s.OWNER and t.TABLE_NAME = s.SEGMENT_NAME and s.SEGMENT_TYPE = 'TABLE'
left outer join (
    select w.TABLE_OWNER,w.TABLE_NAME,max(w.TABLESPACE_NAME) TABLESPACE_NAME
    ,sum(w.BYTES) / 1024/1024 M_BYTE,max(w.LOGGING) logging from 
    (
        select t.TABLE_OWNER,t.TABLE_NAME,t.PARTITION_NAME,t.HIGH_VALUE
        ,t.PARTITION_POSITION,t.TABLESPACE_NAME,t.LOGGING,s.BYTES
        from DBA_TAB_PARTITIONS t
        left outer join dba_segments s on t.TABLE_OWNER = s.OWNER and t.PARTITION_NAME = s.PARTITION_NAME
        where t.TABLE_OWNER ='DABADA'
    ) w group by w.TABLE_OWNER,w.TABLE_NAME
) p
on t.OWNER = p.TABLE_OWNER and t.TABLE_NAME = p.TABLE_NAME
left outer join DBA_PART_TABLES b
on t.OWNER = b.OWNER and t.TABLE_NAME = b.TABLE_NAME
where t.OWNER='DABADA'
and not exists -- マテビューを除く
(select 'X' from DBA_MVIEWS v where t.OWNER = v.OWNER and t.TABLE_NAME = v.MVIEW_NAME)
and not exists -- マテビューログテーブルを除く
(select 'X' from DBA_MVIEW_LOGS l where t.OWNER = l.LOG_OWNER and t.TABLE_NAME = l.LOG_TABLE)
order by t.OWNER,t.TABLE_NAME
;
一覧出力例
                                                           LOGG TEMPO PARTI  PART.    SUBPART.
OWNER    TABLE_NAME       COMMENTS                         ING  RARY  TIONED TYPE     TYPE         M_BYTE
-------- ---------------- -------------------------------- ---- ----- ------ -------- -------- ----------
DABADA   EX02_LIST        エリア別実績                     NO   N     YES    LIST     NONE             16
DABADA   OY_PREFECTURE    都道府県マスタ                   NO   N     NO                            .0625
DABADA   OY_REGION        地域マスタ                       NO   N     NO                            .0625
DABADA   PTRENGEEX01      レンジパーティションサンプル     NO   N     YES    RANGE    NONE            224
DABADA   TABINF           テーブル情報                     NO   Y     NO
DABADA   TBL26            テーブル２６                     NO   N     NO                              176
DABADA   TBL29            一般テーブルサンプルデータ無     NO   N     NO
DABADA   TBL304           一般テーブルサンプル304          YES  N     NO
DABADA   TEMP100          一時表サンプル                   NO   Y     NO

9行が選択されました。

SQL>
SQL*Plus表示用
set lin 300
set pages 100
COLUMN OWNER  FORMAT A8
COLUMN TABLE_NAME FORMAT A16
COLUMN COMMENTS   FORMAT A32
COLUMN TABLESPACE_NAME  FORMAT A12
COLUMN TEMPORARY FORMAT A5
COLUMN TEMPORARY HEADING 'TEMPO|RARY'
COLUMN LOGGING FORMAT A4
COLUMN LOGGING HEADING 'LOGG|ING'
COLUMN COMMENTS FORMAT A32
COLUMN PARTITIONED FORMAT A6
COLUMN PARTITIONED HEADING 'PARTI|TIONED'
COLUMN PARTITIONING_TYPE FORMAT A8
COLUMN PARTITIONING_TYPE HEADING 'PART.|TYPE'
COLUMN SUBPARTITIONING_TYPE FORMAT A8
COLUMN SUBPARTITIONING_TYPE HEADING 'SUBPART.|TYPE'

パーティション一覧

パーティション毎の一覧出力SQL
select t.TABLE_OWNER,t.TABLE_NAME,t.PARTITION_POSITION,t.PARTITION_NAME
--,t.HIGH_VALUE
,t.TABLESPACE_NAME,t.LOGGING
,s.BYTES/1024/1024 M_BYTE
 from DBA_TAB_PARTITIONS t 
left outer join dba_segments s on t.TABLE_OWNER = s.OWNER and t.TABLE_NAME = s.SEGMENT_NAME 
and t.PARTITION_NAME  = s.PARTITION_NAME and s.SEGMENT_TYPE='TABLE PARTITION'
where t.TABLE_OWNER='DABADA'
order by t.TABLE_OWNER,t.TABLE_NAME,t.PARTITION_POSITION
;
一覧出力例
                          PARTITION PARTITION  TABLESPACE
TABLE_OW TABLE_NAME        POSITION NAME       NAME       LOGGING            M_BYTE
-------- ---------------- --------- ---------- ---------- -------------- ----------
DABADA   EX02_LIST                1 AREA_01    TBS02      NO
DABADA   EX02_LIST                2 AREA_02    TBS02      NO                      8
DABADA   EX02_LIST                3 AREA_03    TBS02      NO                      8
DABADA   EX02_LIST                4 AREA_04    TBS02      NO
DABADA   EX02_LIST                5 AREA_05    TBS02      NO
DABADA   EX02_LIST                6 AREA_06    TBS02      NO
DABADA   EX02_LIST                7 AREA_07    TBS02      NO
DABADA   EX02_LIST                8 AREA_08    TBS02      NO
DABADA   PTRENGEEX01              1 DAY1       TBS02      NO
DABADA   PTRENGEEX01              2 DAY2       TBS02      NO                     48
DABADA   PTRENGEEX01              3 DAY3       TBS02      NO
DABADA   PTRENGEEX01              4 DAY4       TBS02      NO                    176

12行が選択されました。

SQL>
SQL*Plus表示用
set lin 300
set pages 100
COLUMN TABLE_OWNER  FORMAT A8
COLUMN TABLE_NAME FORMAT A16
COLUMN PARTITION_POSITION FORMAT 99
COLUMN PARTITION_POSITION  HEADING 'PARTITION|POSITION'
COLUMN PARTITION_NAME FORMAT A10
COLUMN PARTITION_NAME  HEADING 'PARTITION|NAME'
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN TABLESPACE_NAME HEADING 'TABLESPACE|NAME'

全てのテーブル一覧

全てのテーブル一覧出力SQL

以下はDABADAスキーマが持つ、マテビュー、マテビューログテーブル、パーティションテーブルを含む全てのテーブル一覧。（パーティションテーブルについてはパーティション毎のサイズ合計、代表(max値)パーティションの表領域を出力。いくつものディクショナリビューを結合するので少し重くなります。

select t.OWNER,t.TABLE_NAME
,decode(t.PARTITIONED,'YES',p.TABLESPACE_NAME,t.TABLESPACE_NAME) TABLESPACE_NAME
,decode(v.MVIEW_NAME,null,c.COMMENTS,mc.COMMENTS) comments
,decode(v.MVIEW_NAME,null,'TABLE','MVIEW') TYPE 
,t.PARTITIONED
,decode(t.PARTITIONED,'YES',p.LOGGING,t.LOGGING) LOGGING
,t.TEMPORARY
,decode(t.PARTITIONED,'YES',p.M_BYTE,s.BYTES/1024/1024) M_BYTE
from DBA_TABLES t
left outer join DBA_TAB_COMMENTS c
on t.OWNER = c.OWNER  and t.TABLE_NAME = c.TABLE_NAME and c.TABLE_TYPE ='TABLE'
left outer join DBA_MVIEW_COMMENTS mc
on t.OWNER = mc.OWNER and t.TABLE_NAME = mc.MVIEW_NAME
left outer join DBA_SEGMENTS s on t.OWNER = s.OWNER and t.TABLE_NAME = s.SEGMENT_NAME and s.SEGMENT_TYPE='TABLE' 
left outer join DBA_MVIEWS v on t.OWNER = v.OWNER and t.TABLE_NAME = v.MVIEW_NAME 
left outer join (
    select w.TABLE_OWNER,w.TABLE_NAME,max(w.TABLESPACE_NAME) TABLESPACE_NAME
    ,sum(w.BYTES) / 1024/1024 M_BYTE,max(w.LOGGING) logging from 
    (
        select t.TABLE_OWNER,t.TABLE_NAME,t.PARTITION_NAME,t.HIGH_VALUE
        ,t.PARTITION_POSITION,t.TABLESPACE_NAME,t.LOGGING,s.BYTES
        from DBA_TAB_PARTITIONS t
        left outer join dba_segments s on t.TABLE_OWNER = s.OWNER and t.PARTITION_NAME = s.PARTITION_NAME
        where t.TABLE_OWNER ='DABADA'
    ) w group by w.TABLE_OWNER,w.TABLE_NAME
) p
on t.OWNER = p.TABLE_OWNER and t.TABLE_NAME = p.TABLE_NAME
where t.OWNER ='DABADA'
order by t.OWNER,t.TABLE_NAME
;
一覧出力例
                              TABLESPACE                                         PARTI  LOGG TEMPO
OWNER    TABLE_NAME           NAME       COMMENTS                         TYPE   TIONED ING  RARY      M_BYTE
-------- -------------------- ---------- -------------------------------- ------ ------ ---- ----- ----------
DABADA   EX02_LIST            TBS02      エリア別実績                     TABLE  YES    NO   N             16
DABADA   MLOG$_OY_PREFECTURE  TBS02      OY_PREFECTUREログ                TABLE  NO     NO   N          .0625
DABADA   MLOG$_OY_REGION      TBS02      OY_REGIONログ                    TABLE  NO     NO   N          .0625
DABADA   OY_PREFECTURE        TBS02      都道府県マスタ                   TABLE  NO     NO   N          .0625
DABADA   OY_REGION            TBS02      地域マスタ                       TABLE  NO     NO   N          .0625
DABADA   PREFECTURE_F_MMV     TBS02      都道府県マスタON COMMIT版        MVIEW  NO     NO   N          .0625
DABADA   PREFECTURE_MMV       TBS02      都道府県MMV                      MVIEW  NO     NO   N          .0625
DABADA   PTRENGEEX01          TBS02      レンジパーティションサンプル     TABLE  YES    NO   N            224
DABADA   TABINF                          テーブル情報                     TABLE  NO     NO   Y
DABADA   TBL26                TBS02      テーブル２６                     TABLE  NO     NO   N            176
DABADA   TBL29                TBS02      一般テーブルサンプルデータ無     TABLE  NO     NO   N
DABADA   TBL304               TBS01      一般テーブルサンプル304          TABLE  NO     YES  N
DABADA   TEMP100                         一時表サンプル                   TABLE  NO     NO   Y

13行が選択されました。

SQL>
SQL*Plus表示用
set lin 300
set pages 100
COLUMN OWNER FORMAT A8
COLUMN TABLE_NAME FORMAT A20
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN TABLESPACE_NAME  HEADING 'TABLESPACE|NAME'
COLUMN COMMENTS FORMAT A32
COLUMN TYPE FORMAT A6
COLUMN PARTITIONED FORMAT A3
COLUMN LOGGING FORMAT A3
COLUMN TEMPORARY FORMAT A3
COLUMN TABLE_OWNER FORMAT A8
COLUMN PARTITION_POSITION FORMAT 99
COLUMN PARTITION_POSITION  HEADING 'PARTITION|POSITION'
COLUMN PARTITION_NAME FORMAT A8
COLUMN HIGH_VALUE FORMAT A56
COLUMN PARTITIONING_TYPE FORMAT A8
COLUMN SUBPARTITIONING_TYPE FORMAT A8
COLUMN PARTITIONING_TYPE HEADING 'PART.|TYPE'
COLUMN SUBPARTITIONING_TYPE HEADING 'SUBPART.|TYPE'
COLUMN PARTITIONED FORMAT A6
COLUMN PARTITIONED HEADING 'PARTI|TIONED'
COLUMN TEMPORARY FORMAT A5
COLUMN TEMPORARY HEADING 'TEMPO|RARY'
COLUMN LOGGING FORMAT A4
COLUMN LOGGING HEADING 'LOGG|ING'
スポンサーリンク
sooni
タイトルとURLをコピーしました