set lines 200
col index_name from a30
col column_name for a30
select index_name from dba_indexes where table_name='&TABLE_NAME' and owner='&TABLE_OWNER';
select index_name from user_indexes where table_name='&TABLE_NAME';
select OWNER,INDEX_NAME,STATUS,TABLE_NAME from dba_indexes where TABLE_NAME='&tablename';
Find the list of top index size corresponding to a table.
SELECT idx.table_name,bytes/1024/1024/1024
FROM dba_segments seg,
dba_indexes idx
where idx.table_name='&TABLE_NAME'
AND idx.index_name = seg.segment_name
GROUP BY idx.table_name order by 1;
select segment_name,TABLESPACE_NAME ,segment_type, bytes/1024/1024/1024 size_gb from dba_segments where segment_name = '&segment_name' or segment_name in (select index_name from dba_indexes where table_name='&tablename' and table_owner='&owner');
Check Index Column on a Table in Oracle.
set lines 200
col index_name from a30
col column_name for a30
select index_name, column_name from dba_ind_columns where table_name='&TABLE_NAME' and TABLE_OWNER='&OWNER';
select index_name, column_name from user_ind_columns where table_name='&TABLE_NAME';
Find total index size of respective tables in a schema.
SELECT idx.table_name, SUM(bytes/1024/1024/1024)
FROM dba_segments seg,
dba_indexes idx
WHERE idx.table_owner = 'HR'
AND idx.owner = seg.owner
AND idx.index_name = seg.segment_name
GROUP BY idx.table_name order by 1
select sum(bytes)/1024/1024 as "Index Size (MB)" from dba_segments where segment_name='&INDEX_NAME';
Find the list of index which is bigger than table size.
set lines 200
col "Table" format a30
col "Index" format a30
col "Owner" format a20
col tab_gb format 999999.99
col ind_gb format 999999.99
select * from (
select a.owner, a.segment_name "Table", a.tab_gb, b.index_name "Index", b.ind_gb
from
(select owner,segment_name, round(sum(bytes/(102410241024)),2) TAB_GB
from dba_segments where segment_type='TABLE'
and owner not in ('SYS','SYSTEM')
group by owner,segment_name) a,
(select s.owner,i.table_name, i.index_name, round(sum(bytes/(102410241024)),2) IND_GB
from dba_indexes i, dba_segments s
where s.segment_type='INDEX'
and s.segment_name=i.index_name
and s.owner not in ('SYS','SYSTEM')
group by s.owner,i.table_name, i.index_name) b
where a.owner=b.owner
and a.segment_name=b.table_name
and a.tab_gb < b.ind_gb
order by b.ind_gb desc
) where rownum<6;
