Wednesday, March 25, 2020

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

No comments:

Post a Comment