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

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;