题目部分
分区表常用数据字典视图有哪些?
答案部分
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/
● 本系列题目来源于作者的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
● 题目解答若有不当之处,还望各位朋友批评指正,共同进步