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