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 |
No comments:
Post a Comment