分区表常用数据字典视图有哪些？

Oracle分区表相关数据字典视图如下所示：

（1）显示数据库所有分区表的信息：DBA_PART_TABLES。

（2）显示表分区信息，显示数据库所有分区表的详细分区信息：DBA_TAB_PARTITIONS。

（3）显示子分区信息，显示数据库所有复合分区表的子分区信息：DBA_TAB_SUBPARTITIONS。

（4）显示分区列，显示数据库所有分区表的分区列信息：DBA_PART_KEY_COLUMNS。

（5）显示子分区列，显示数据库所有分区表的子分区列信息：DBA_SUBPART_KEY_COLUMNS。

关于分区表的一些数据字典，作者写了以下3个脚本：

所有的分区表：

SELECT d."TABLE_OWNER",

       d."TABLE_NAME",

       decode(dp.subpartitioning_type,

              'NONE',

              '',

              dp.partitioning_type || '-' || dp.subpartitioning_type) all_partitioning_type,

       dp.partitioning_type,

       (SELECT to_char(wm_concat(dd.column_name))

        FROM   DBA_PART_KEY_COLUMNS dd

        WHERE  dd.name = d.table_name

        AND    dd.object_type = 'TABLE'

        AND    dd.owner = d.table_owner) PART_KEY_COLUMNS,

       dp.partition_count,

       dp.interval,

       dp.partitioning_key_count,

       dp.status,

       d."PARTITION_NAME",

       d.segment_created,

       (SELECT nb.CREATED

        FROM   dba_objects nb

        WHERE  nb.OWNER = d.table_owner

        AND    nb.SUBOBJECT_NAME = d.partition_name

        AND    nb.OBJECT_NAME = d.table_name) partition_CREATED,

       d. "PARTITION_POSITION",

       d."TABLESPACE_NAME",

       d."LAST_ANALYZED",

       (SELECT nb.LAST_DDL_TIME

        FROM   dba_objects nb

        WHERE  nb.OWNER = d.table_owner

        AND    nb.SUBOBJECT_NAME = d.partition_name

        AND    nb.OBJECT_NAME = d.table_name) LAST_DDL_TIME,

       d."NUM_ROWS",

       d."BLOCKS",

       dts.STALE_STATS, --统计信息是否变的陈旧  

       dtm.inserts,

       dtm.updates,

       dtm.deletes,

       dtm.truncated,

       dtm.drop_segments,

       D.HIGH_VALUE,

       d."BUFFER_POOL",

       d."GLOBAL_STATS",

       d."USER_STATS",

       d."EMPTY_BLOCKS",

       d."AVG_SPACE",

       d."CHAIN_CNT",

       d."SUBPARTITION_COUNT",

       dp.def_subpartition_count,

       dp.subpartitioning_type,

       dp.subpartitioning_key_count,

       dp.def_tablespace_name,

       dp.def_pct_free,

       dp.def_pct_used,

       dp.def_ini_trans,

       dp.def_max_trans,

       dp.def_initial_extent,

       dp.def_next_extent,

       dp.def_min_extents,

       dp.def_max_extents,

       dp.def_pct_increase,

       dp.def_freelists,

       dp.def_freelist_groups,

       dp.def_logging,

       dp.def_compression,

       dp.def_buffer_pool,

       d."LOGGING",

       d."PCT_FREE",

       d."PCT_USED",

       d."INI_TRANS",

       d."MAX_TRANS",

       d."INITIAL_EXTENT",

       d."NEXT_EXTENT",

       d."MIN_EXTENT",

       d."MAX_EXTENT",

       d."PCT_INCREASE",

       d."FREELISTS",

       d."FREELIST_GROUPS",

       d."COMPRESSION",

       d."AVG_ROW_LEN",

       d."SAMPLE_SIZE"

FROM   DBA_TAB_PARTITIONS D

JOIN   Dba_Part_Tables dp

ON     (d.table_name = dp.table_name AND d.table_owner = dp.owner)

JOIN   dba_tab_statistics dts

ON     (d.table_name = dts.TABLE_NAME AND d.table_owner = dts.owner AND

       dts.PARTITION_NAME = d.partition_name AND

       dts.OBJECT_TYPE = 'PARTITION')

LEFT   OUTER JOIN (SELECT DO.OWNER,

                          DO.OBJECT_NAME,

                          DO.SUBOBJECT_NAME

                   FROM   sys.tabpart$ t,

                          dba_objects  do

                   WHERE  t.obj# = do.OBJECT_ID) TT

ON     (D.TABLE_NAME = TT.OBJECT_NAME AND

       D.PARTITION_NAME = TT.SUBOBJECT_NAME AND D.TABLE_OWNER = TT.OWNER)

LEFT   OUTER JOIN (SELECT dtm.table_owner,

                          dtm.table_name,

                          dtm.partition_name,

                          SUM(dtm.updates) updates,

                          SUM(dtm.inserts) inserts,

                          SUM(dtm.deletes) deletes,

                          SUM(dtm.drop_segments) drop_segments,

                          MAX(dtm.truncated) truncated

                   FROM   dba_tab_modifications dtm

                   GROUP  BY dtm.table_owner,

                             dtm.table_name,

                             dtm.partition_name) dtm

ON     (d.table_owner = dtm.table_owner AND d.TABLE_NAME = dtm.table_name AND

       dtm.partition_name = d.partition_name)

ORDER  BY d.table_name,

          d.partition_position,

          d.partition_name;

子分区的信息：

SELECT d."TABLE_OWNER",

       d."TABLE_NAME",

       dp.partitioning_type,

       (SELECT to_char(wm_concat(dd.column_name))

        FROM   DBA_PART_KEY_COLUMNS dd

        WHERE  dd.name = d.table_name

        AND    dd.object_type = 'TABLE'

        AND    dd.owner = d.table_owner) PART_KEY_COLUMNS,

       dp.partition_count,

       dp.partitioning_key_count,

       dp.status,

       d."PARTITION_NAME",

       d.subpartition_name,

       wd.BYTES partition_size,

       d.segment_created,

       d.subpartition_position,

       d."TABLESPACE_NAME",

       d."LAST_ANALYZED",

       d."NUM_ROWS",

       d."BLOCKS",

       dts.STALE_STATS,

        dtm.inserts,

       dtm.updates,

       dtm.deletes,

       dtm.truncated,

       dtm.drop_segments,

       d."BUFFER_POOL",

       d."GLOBAL_STATS",

       d."USER_STATS",

       d."EMPTY_BLOCKS",

       d."AVG_SPACE",

       d."CHAIN_CNT",

       dp.def_subpartition_count,

       dp.subpartitioning_type,

       (SELECT dd.column_name

        FROM   dba_subpart_key_columns dd

        WHERE  dd.name = d.table_name

        AND    dd.owner = d.table_owner) subPART_KEY_COLUMNS,

       dp.subpartitioning_key_count,

       dp.def_tablespace_name,

       dp.def_pct_free,

       dp.def_pct_used,

       dp.def_ini_trans,

       dp.def_max_trans,

       dp.def_initial_extent,

       dp.def_next_extent,

       dp.def_min_extents,

       dp.def_max_extents,

       dp.def_pct_increase,

       dp.def_freelists,

       dp.def_freelist_groups,

       dp.def_logging,

       dp.def_compression,

       dp.def_buffer_pool,

       d."LOGGING",

       d."PCT_FREE",

       d."PCT_USED",

       d."INI_TRANS",

       d."MAX_TRANS",

       d."INITIAL_EXTENT",

       d."NEXT_EXTENT",

       d."MIN_EXTENT",

       d."MAX_EXTENT",

       d."PCT_INCREASE",

       d."FREELISTS",

       d."FREELIST_GROUPS",

       d."COMPRESSION",

       d."AVG_ROW_LEN",

       d."SAMPLE_SIZE"

FROM   DBA_TAB_SUBPARTITIONS D

JOIN   Dba_Part_Tables dp

ON     (d.table_name = dp.table_name AND d.table_owner = dp.owner )

JOIN   dba_tab_statistics dts

ON     (dts.OWNER = d.table_owner AND dts.TABLE_NAME = d.table_name AND

       d.partition_name = dts.PARTITION_NAME AND

       d.subpartition_name = dts.SUBPARTITION_NAME

       and dts.OBJECT_TYPE='SUBPARTITION')

LEFT   OUTER JOIN dba_segments wd

ON     (d.table_name = wd.segment_name AND

       wd.partition_name = d.subpartition_name AND

       wd.owner = d.table_owner AND wd.segment_type = 'TABLE SUBPARTITION')

LEFT   OUTER JOIN dba_tab_modifications dtm

ON     (d.table_owner = dtm.table_owner AND d.TABLE_NAME = dtm.table_name AND

       dtm.partition_name = d.partition_name and dtm.subpartition_name=d.subpartition_name)

ORDER  BY d.table_name,

          d.partition_name,

          d.subpartition_name;

分区索引信息：

SELECT di.table_owner table_owner,

       DP.table_name table_name,

       d."INDEX_OWNER",

       d."INDEX_NAME",

       di.index_type index_type,

       d."COMPOSITE",

       d."PARTITION_NAME",

       CASE

           WHEN d.subpartition_count > 0 THEN

            '含子分区索引'

       END AS is_subpartition,

       d.segment_created,

       d."SUBPARTITION_COUNT",

       d."HIGH_VALUE",

       d."HIGH_VALUE_LENGTH",

       d."PARTITION_POSITION",

       d."STATUS",

       d."TABLESPACE_NAME",

       d."PCT_FREE",

       d."INI_TRANS",

       d."MAX_TRANS",

       d."INITIAL_EXTENT",

       d."NEXT_EXTENT",

       d."MIN_EXTENT",

       d."MAX_EXTENT",

       d."PCT_INCREASE",

       d."FREELISTS",

       d."FREELIST_GROUPS",

       d."LOGGING",

       d."COMPRESSION",

       d."BLEVEL",

       d."LEAF_BLOCKS",

       d."DISTINCT_KEYS",

       d."AVG_LEAF_BLOCKS_PER_KEY",

       d."AVG_DATA_BLOCKS_PER_KEY",

       d."CLUSTERING_FACTOR",

       d."NUM_ROWS",

       DIS.STALE_STATS,

       d."SAMPLE_SIZE",

       d."LAST_ANALYZED",

       d."BUFFER_POOL",

       d."USER_STATS",

       d."PCT_DIRECT_ACCESS",

       d."GLOBAL_STATS",

       d."DOMIDX_OPSTATUS",

       d."PARAMETERS",

       dp.interval

FROM   dba_ind_partitions d

JOIN   DBA_PART_INDEXES DP

ON     (D.INDEX_NAME = DP.index_name)

LEFT   OUTER JOIN DBA_INDEXES di

ON     (di.index_name = D.INDEX_NAME AND di.table_name = DP.table_name AND

       DI.owner = D.INDEX_OWNER)

LEFT   OUTER JOIN dba_ind_statistics DIS

ON     (D.INDEX_OWNER = DIS.OWNER AND D.INDEX_NAME = DIS.INDEX_NAME AND

       D.PARTITION_NAME = DIS.PARTITION_NAME AND

       DIS.OBJECT_TYPE = 'PARTITION')

LEFT   OUTER JOIN (SELECT DO.OWNER,

                          DO.OBJECT_NAME,

                          DO.SUBOBJECT_NAME

                   FROM   sys.indpartv$ t,

                          dba_objects   do

                   WHERE  t.obj# = do.OBJECT_ID

                   AND    do.OBJECT_TYPE = 'INDEX PARTITION') TT

ON     (D.Index_Name = TT.OBJECT_NAME AND

       D.PARTITION_NAME = TT.SUBOBJECT_NAME AND D.Index_Owner = TT.OWNER)

WHERE  d.index_owner NOT IN ('SYS', 'SYSTEM')

ORDER  BY dp.table_name,

          d.index_name,

          d.partition_position;

子分区索引：

SELECT DP.OWNER                    TABLE_OWNER,

       DP.TABLE_NAME               TABLE_NAME,

       d."INDEX_OWNER",

       d."INDEX_NAME",

       d."PARTITION_NAME",

       D.SUBPARTITION_NAME,

       D.SUBPARTITION_POSITION,

       d."NUM_ROWS",

       DIS.STALE_STATS,

       d."SAMPLE_SIZE",

       d."LAST_ANALYZED",

       d."STATUS",

       d."TABLESPACE_NAME",

       D.SEGMENT_CREATED,

       d."HIGH_VALUE",

       d."HIGH_VALUE_LENGTH",

       d."PCT_FREE",

       d."INI_TRANS",

       d."MAX_TRANS",

       d."INITIAL_EXTENT",

       d."NEXT_EXTENT",

       d."MIN_EXTENT",

       d."MAX_EXTENT",

       d."PCT_INCREASE",

       d."FREELISTS",

       d."FREELIST_GROUPS",

       d."LOGGING",

       d."COMPRESSION",

       d."BLEVEL",

       d."LEAF_BLOCKS",

       d."DISTINCT_KEYS",

       d."AVG_LEAF_BLOCKS_PER_KEY",

       d."AVG_DATA_BLOCKS_PER_KEY",

       d."CLUSTERING_FACTOR",

       d."BUFFER_POOL",

       d."USER_STATS",

       d."GLOBAL_STATS"

  FROM DBA_IND_SUBPARTITIONS D

  JOIN DBA_PART_INDEXES DP

    ON (D.INDEX_NAME = DP.INDEX_NAME AND

       D.INDEX_OWNER NOT IN ('SYS', 'SYSTEM'))

  LEFT OUTER JOIN DBA_IND_STATISTICS DIS

    ON (D.INDEX_OWNER = DIS.OWNER AND D.INDEX_NAME = DIS.INDEX_NAME AND

       D.PARTITION_NAME = DIS.PARTITION_NAME AND

       D.SUBPARTITION_NAME = DIS.SUBPARTITION_NAME AND

       DIS.OBJECT_TYPE = 'INDEX SUBPARTITION')

 ORDER BY D.INDEX_NAME, D.PARTITION_NAME;

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

---------------优质麦课------------