Oracle Table and index size

COLUMN TABLE_NAME FORMAT A32

COLUMN OBJECT_NAME FORMAT A32

COLUMN OWNER FORMAT A10

SELECT

owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type = ‘TABLE’

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND   s.owner = i.owner

AND   s.segment_type = ‘INDEX’

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND   s.owner = l.owner

AND   s.segment_type = ‘LOBSEGMENT’

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND   s.owner = l.owner

AND   s.segment_type = ‘LOBINDEX’)

WHERE owner in UPPER(‘&owner’)

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */

ORDER BY SUM(bytes) desc

;

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: