分区表 分区索引对应关系 - Oracle-fans - 博客园

​

select   t3.table_name tab_name,    t3.partition_name tab_part_name,t2.locality  ind_locality,t1.index_name ind_name,t1.partition_name ind_part_name,t1.status ind_part_stat,t1.tablespace_name tbs_name

from user_ind_partitions t1, user_part_indexes t2  ,user_tab_partitions t3

where t1.index_name in ('SK_DEVICE_RECOVER_LOCAL') and t1.index_name=t2.index_name  and t2.table_name=t3.table_name and t1.partition_position = t3.partition_position

order by t1.index_name,t3.partition_name;

方法二：

 set pagesize 1000

 set linesize 1000

 col partition_name for a15

 col partitioning_type for a25

 col index_name for a15

 select t2.locality,

           t2.partitioning_type,

           t1.index_name,

           t1.partition_name,

           t1.status,

           t1.tablespace_name

      from user_ind_partitions t1, user_part_indexes t2

     where t1.index_name in ('C31$FPDM_FPHM', 'C31$JSSJ')

       and t1.index_name = t2.index_name

    order by index_name, partition_name;

/*

LOCALI PARTITIONING_TYPE         INDEX_NAME      PARTITION_NAME  STATUS   TABLESPACE_NAME

------ ------------------------- --------------- --------------- -------- ------------------------------

GLOBAL HASH                      C31$FPDM_FPHM   PART1801        USABLE   LVSKSK_I_M_1801

GLOBAL HASH                      C31$FPDM_FPHM   PART1802        USABLE   LVSKSK_I_M_1802

GLOBAL HASH                      C31$FPDM_FPHM   PART1803        USABLE   LVSKSK_I_M_1803

GLOBAL HASH                      C31$FPDM_FPHM   PART1804        USABLE   LVSKSK_I_M_1804

GLOBAL HASH                      C31$FPDM_FPHM   PART1805        USABLE   LVSKSK_I_M_1805

GLOBAL HASH                      C31$FPDM_FPHM   SYS_P72         USABLE   LVSKSK_T_GY

LOCAL  RANGE                     C31$JSSJ        P_201609        USABLE   LVSKSK_I_JS_P

LOCAL  RANGE                     C31$JSSJ        P_201801        USABLE   LVSKSK_I_M_1801

LOCAL  RANGE                     C31$JSSJ        P_201802        USABLE   LVSKSK_I_M_1802

LOCAL  RANGE                     C31$JSSJ        P_201803        USABLE   LVSKSK_I_M_1803

LOCAL  RANGE                     C31$JSSJ        P_201804        USABLE   LVSKSK_I_M_1804

LOCAL  RANGE                     C31$JSSJ        P_201805        USABLE   LVSKSK_I_M_1805