How to list index partitions and their sizes

To list index partitions for index SALES_IDX1:

SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXPART.INDEX_NAME='SALES_IDX1'
AND    S.SEGMENT_TYPE='INDEX PARTITION'
-- To only list partitions in tablespace DATA1, uncomment the following line:
-- AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX1	SYS_P177667	DATA1	8
SH	SALES_IDX1	SYS_P177518	DATA1	5
SH	SALES_IDX1	SYS_P44844	DATA1	3
SH	SALES_IDX1	SYS_P44663	DATA1	2
SH	SALES_IDX1	SYS_P177677	DATA1	2

To list all index partitions for tablespace DATA1:

SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
AND    S.SEGMENT_TYPE='INDEX PARTITION'
AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	GB
SH	SALES_IDX1	SYS_P177667	8
SH	SALES_IDX1	SYS_P177518	5
SH	SALES_IDX1	SYS_P44844	3
SH	SALES_IDX1	SYS_P177677	2
SH	SALES_IDX1	SYS_P44663	2
SH	SALES_IDX2	SYS_P179608	1
SH	SALES_IDX2	SYS_P178334	1
SH	SALEX_IDX2	SYS_P178459	1
SH	SALES_IDX3	SYS_P28534	0
SH	SALES_IDX3	SYS_P50905	0

To list the names of all partitioned indexes and their total sizes, in a particular tablespace:

SELECT DISTINCT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.TABLESPACE_NAME, 
    (SELECT ROUND(SUM(S.BYTES)/1024/1024/1024) 
     FROM DBA_SEGMENTS S
     WHERE S.SEGMENT_NAME = IDXPART.INDEX_NAME
     ) GB
FROM   DBA_IND_PARTITIONS IDXPART 
WHERE  IDXPART.TABLESPACE_NAME='DATA1'
ORDER BY 4 DESC;

INDEX_OWNER	INDEX_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX1	DATA1	567
SH	SALES_IDX2	DATA1	511
SH	SALES_IDX3	DATA1	331

To check which tablespaces the different partitions in an index reside in:

SELECT TABLESPACE_NAME,COUNT(*)
FROM DBA_IND_PARTITIONS
WHERE INDEX_NAME='SALES_IDX4'
GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME	COUNT(*)
DATA1	13
DATA2	832