【DB笔试面试473】分区表常用数据字典视图有哪些？

• 2019 年 10 月 11 日
• 筆記

题目部分

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

答案部分

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程序员面试笔试宝典》，作者：李华荣。

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步