Skip to content
January 10, 2013 / doganay

INDEXES WHICH ARE BIGGER THAN THEIR TABLES

--based on a script of John Watson
--written by Mustafa DOGANAY - 2012
--https://doganay.wordpress.com
select owner "TABLE_OWNER", tablename "TABLE_NAME", tablesize "TABLE SIZE (MB)", indexname "INDEX_NAME", indexsize "INDEX SIZE (MB)", indexsize/tablesize "INDEX/TABLE" from
(
with
tabs as (select owner, segment_name tablename,sum(bytes/1024/1024) tablesize from dba_segments where segment_type='TABLE' group by owner, segment_name),
inds as (select i.owner, i.index_name indexname, i.table_name tablename, sum(s.bytes/1024/1024) indexsize from dba_indexes i join dba_segments s on (i.owner=s.owner and i.index_name=s.segment_name) group by i.owner, i.index_name, i.table_name)
select * from tabs natural join inds where indexsize > tablesize and indexsize>100
)
order by indexsize*indexsize/tablesize desc
/

https://forums.oracle.com/forums/thread.jspa?threadID=2484121&tstart=45

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: