How to find table size?

Hello,

if you want to see all table sizes in oracle database you can use this code.

SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024) GB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
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 IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
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 in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION')
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')
GROUP BY table_name, owner
order by sum(bytes) desc;

Hits: 20

3 thoughts on “How to find table size?

  1. You actually make it seem really easy together with your presentation but I to find this matter to be actually one thing which I think I would never understand. It seems too complicated and very vast for me. I am having a look forward to your subsequent post, I’ll attempt to get the cling of it!

  2. I have fun with, cause I found exactly what I was taking a look for. You have ended my four day lengthy hunt! God Bless you man. Have a great day. Bye

Leave a Reply

Your email address will not be published. Required fields are marked *