Oracle 数据库针对分区的信息，通过多个数据字典视图来维护，所以在获取信息时，经常会困惑 DBA 们。
例如，如何获取 分区索引的类型，如何判断一个索引，是 全局分区索引，还是本地分区索引？
通过 DBA_PART_INDEXES 中的 LOCALITY 字段就可以判断：
SQL> select distinct (locality) from dba_part_indexes;
LOCALI
------
LOCAL
LOCAL 意味着是本地索引；GLOBAL 则意味着是全局索引。
DBA_PART_INDEXES 和 ALL_PART_INDEXES、USER_PART_INDEXES 视图的字段相同，分别展示的是 数据库（DBA级别）、用户权限级别（ALL）和 用户所属级别（USER）的分区索引信息。
至于分区类型等，可以通过这个视图查询获得：
SQL> select TABLE_NAME,PARTITIONING_TYPE,LOCALITY from dba_part_indexes where rownum < 10;
TABLE_NAME PARTITION LOCALI
-------------------------------------------------- --------- ------
STREAMS$_APPLY_SPILL_MSGS_PART LIST LOCAL
STREAMS$_APPLY_SPILL_MSGS_PART LIST LOCAL
LOGMNRC_CONCOL_GG RANGE LOCAL
LOGMNRC_CON_GG RANGE LOCAL
LOGMNRC_CON_GG RANGE LOCAL
LOGMNRC_CON_GG RANGE LOCAL
LOGMNRC_GSBA RANGE LOCAL
LOGMNRC_GSII RANGE LOCAL
LOGMNRC_GTCS RANGE LOCAL
视图信息引入如下，供参考：
| Column | Datatype | NULL | Description |
|---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the partitioned index |
INDEX_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the partitioned index |
TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the partitioned table |
PARTITIONING_TYPE |
VARCHAR2(7) |
Type of partitioning method:
|
|
SUBPARTITIONING_TYPE |
VARCHAR2(7) |
Type of composite partitioning method:
|
|
PARTITION_COUNT |
NUMBER |
NOT NULL |
Number of partitions in the index |
DEF_SUBPARTITION_COUNT |
NUMBER |
For a composite-partitioned index, the default number of subpartitions, if specified | |
PARTITIONING_KEY_COUNT |
NUMBER |
NOT NULL |
Number of columns in the partitioning key |
SUBPARTITIONING_KEY_COUNT |
NUMBER |
For a composite-partitioned index, the number of columns in the subpartitioning key | |
LOCALITY |
VARCHAR2(6) |
Indicates whether the partitioned index is local (LOCAL) or global (GLOBAL) |
|
ALIGNMENT |
VARCHAR2(12) |
Indicates whether the partitioned index is PREFIXED or NON_PREFIXED |
|
DEF_TABLESPACE_NAME |
VARCHAR2(30) |
For a local index, the default tablespace to be used when adding or splitting a table partition | |
DEF_PCT_FREE |
NUMBER |
NOT NULL |
For a local index, the default PCTFREE value to be used when adding a table partition |
DEF_INI_TRANS |
NUMBER |
NOT NULL |
For a local index, the default INITRANS to be used when adding a table partition |
DEF_MAX_TRANS |
NUMBER |
NOT NULL |
For a local index, the default MAXTRANS to be used when adding a table partition |
DEF_INITIAL_EXTENT |
VARCHAR2(40) |
NOT NULL |
For a local index, the default INITIAL value (in Oracle blocks) to be used when adding a table partition, or DEFAULT if no INITIAL value was specified |
DEF_NEXT_EXTENT |
VARCHAR2(40) |
NOT NULL |
For a local index, the default NEXT (in Oracle blocks), or DEFAULT if no NEXT value was specified |
DEF_MIN_EXTENTS |
VARCHAR2(40) |
NOT NULL |
For a local index, the default MINEXTENTS value to be used when adding a table partition, or DEFAULT if no MINEXTENTS value was specified |
DEF_MAX_EXTENTS |
VARCHAR2(40) |
NOT NULL |
For a local index, the default MAXEXTENTS value to be used when adding a table partition, or DEFAULT if no MAXEXTENTS value was specified |
DEF_PCT_INCREASE |
VARCHAR2(40) |
NOT NULL |
For a local index, the default PCTINCREASE value to be used when adding a table partition, or DEFAULT if no PCTINCREASE value was specified |
DEF_FREELISTS |
NUMBER |
NOT NULL |
For a local index, the default FREELISTS value to be used when adding a table partition, or DEFAULT if no FREELISTS value was specified |
DEF_FREELIST_GROUPS |
NUMBER |
NOT NULL |
For a local index, the default FREELIST GROUPS value to be used when adding a table partition, or DEFAULT if no FREELIST GROUPS value was specified |
DEF_LOGGING |
VARCHAR2(7) |
For a local index, the default LOGGING attribute to be used when adding a table partition, or DEFAULT if no LOGGING attribute was specified |
|
DEF_BUFFER_POOL |
VARCHAR2(7) |
For a local index, the default buffer pool to be used when adding a table partition | |
DEF_PARAMETERS |
VARCHAR2(1000) |
Default parameter string for domain indexes |
最后修改时间：2020-04-10 15:56:07
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
