查看分区索引是全局还是本地

dba_ind_partitions 描述了每个分区索引的分区情况，以及统计信息
dba_part_indexes 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)
dba_indexes minus dba_part_indexes （minus操作）可以得到每个表上有哪些非分区索引
[code]SQL> create table ta(c1 int,c2 varchar2(16),c3 varchar2(64),
2 c4 int ,constraint pk_ta primary key (c1))
3 partition by range(c1)(partition p1 values less than (10000000),
partition p2 values less than (20000000),
partition p3 values less than (30000000),
partition p4 values less than (maxvalue)); 4 5 6

Table created.

SQL> create index idx_ta_c4 on ta(c4) global partition by range(c4)
(partition ip1 values less than(10000),partition ip2 values less than(20000),partition ip3 values less than(maxvalue));
2
Index created.

SQL> create index idx_ta_c2 on ta(c2) local (partition p1,partition p2,partition p3,partition p4);

Index created.

SQL>
SQL> desc user_part_indexes
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
PARTITIONING_TYPE VARCHAR2(9)
SUBPARTITIONING_TYPE VARCHAR2(9)
PARTITION_COUNT NOT NULL NUMBER
DEF_SUBPARTITION_COUNT NUMBER
PARTITIONING_KEY_COUNT NOT NULL NUMBER
SUBPARTITIONING_KEY_COUNT NUMBER
LOCALITY VARCHAR2(6)
ALIGNMENT VARCHAR2(12)
DEF_TABLESPACE_NAME VARCHAR2(30)
DEF_PCT_FREE NOT NULL NUMBER
DEF_INI_TRANS NOT NULL NUMBER
DEF_MAX_TRANS NOT NULL NUMBER
DEF_INITIAL_EXTENT VARCHAR2(40)
DEF_NEXT_EXTENT VARCHAR2(40)
DEF_MIN_EXTENTS VARCHAR2(40)
DEF_MAX_EXTENTS VARCHAR2(40)
DEF_MAX_SIZE VARCHAR2(40)
DEF_PCT_INCREASE VARCHAR2(40)
DEF_FREELISTS NOT NULL NUMBER
DEF_FREELIST_GROUPS NOT NULL NUMBER
DEF_LOGGING VARCHAR2(7)
DEF_BUFFER_POOL VARCHAR2(7)
DEF_FLASH_CACHE VARCHAR2(7)
DEF_CELL_FLASH_CACHE VARCHAR2(7)
DEF_PARAMETERS VARCHAR2(1000)
INTERVAL VARCHAR2(1000)

SQL> select INDEX_NAME,LOCALITY from user_part_indexes where table_name ='TA';

INDEX_NAME LOCALI
------------------------------ ------
IDX_TA_C4 GLOBAL
IDX_TA_C2 LOCAL

SQL>[/code]