Simple SQL to list

The following SQL lists the indexes defined on a table, along with the columns and their positioning:

SELECT I.INDEX_NAME,I.INDEX_TYPE,I.NUM_ROWS,I.DEGREE, C.COLUMN_NAME,C.COLUMN_POSITION
FROM DBA_INDEXES I JOIN DBA_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.OWNER='MYSCHEMA'
AND I.OWNER = C.INDEX_OWNER
AND I.TABLE_NAME='MYTABLE'
ORDER BY I.INDEX_NAME, C.COLUMN_POSITION;

How to list index sizes

For non-partitioned global indexes in a specific schema:

select i.index_name, s.tablespace_name,round(sum(bytes)/1024/1024/1024) "GB"
from dba_indexes i join dba_segments s
on (i.index_name = s.segment_name)
where i.owner='SH'
and i.partitioned='NO'
group by i.index_name,s.tablespace_name
order by 3 desc;

INDEX_NAME	TABLESPACE_NAME	GB
AN_EEG_IDX	SH	2406
PK_EEG	SH	994
UIDX_REG_EEG	SH	502
IDX_REL_IDX	SH	156

For non-partitioned global indexes on a specific table:

select i.index_name, s.tablespace_name,round(sum(bytes)/1024/1024/1024) "GB"
from dba_indexes i join dba_segments s
on (i.index_name = s.segment_name)
where i.owner='SH'
and i.partitioned='NO'
and i.table_name = 'ACCOUNTING'
group by i.index_name,s.tablespace_name
order by 2 desc;

INDEX_NAME	TABLESPACE_NAME	GB
IDX_ACC1	SH	120
PK_ACC	SH	994

For partitioned indexes on a specific table:

select s.segment_name,round(sum(s.bytes)/1024/1024/1024,2) "GB" 
from dba_segments s
where segment_name IN (select unique index_name 
                        from dba_indexes 
                        where table_name='SALES' 
                        and PARTITIONED='YES'
                        and index_type <> 'LOB'
                        and owner='SH')
group by s.segment_name;

SEGMENT_NAME	GB
IDX_UK1	28
IDX_ACC_UK2	78,24

For all partitioned indexes in a specific schema:

select i.index_name,i.table_name,s.tablespace_name,round(sum(s.bytes)/1024/1024/1024) "GB"
from dba_indexes i join dba_segments s
on (i.index_name = s.segment_name)
where i.owner='SH'
and i.partitioned='YES'
and i.index_type <> 'LOB'
group by i.index_name,i.table_name,s.tablespace_name
order by 2 ;

INDEX_NAME	TABLE_NAME	TABLESPACE_NAME	GB
IDX_OP_AA	ENTITY	SH	260
IDX_OP_NA	ENTITY_HISTORY	SH	1082
IDX_VER_AA	EVENT	SH	28
IDX_VER_AA_HIST	EVENT	SH	78
IDX_WW_UK2	RELATION	SH	226
IDX_RELNO	RELATION_ENTITY	SH	350

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

How to list index subpartitions and their sizes

SELECT IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='SALES'
AND    IDXSUBPART.TABLESPACE_NAME='SH'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION'
GROUP BY IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME;

Output:

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	SUBPARTITION_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX_SK1	SYS_P18153	SYS_SUBP14709	DATA1	1
SH	SALES_IDX_SK1	SYS_P18157	SYS_SUBP14831	DATA1	1
SH	SALES_IDX_SK1	SYS_P18158	SYS_SUBP14862	DATA2	25
SH	SALES_IDX_SK1	SYS_P18157	SYS_SUBP14832	DATA1	0
SH	SALES_IDX_SK1	SYS_P18142	SYS_SUBP12409	DATA2	32
SH	SALES_IDX_SK1	SYS_P18139	SYS_SUBP12414	DATA1	1