I lost a bunch of scripts I've accumulated for a little less than a decade when I was recently issued a new laptop. One of them is the script to get the size of the table which was shared to me by our DBA. So when I found out that I had a copy of it in my draft, I decided to share it.
SELECT owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg, ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent 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') 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 ;
The query above is quite handy especially if you need to know how much space you need for a project. I have a recent case whereby I need to size up the space needed to add in our read-only database which is being populated via Oracle's Golden Gate. In my current job scope, I need to consider that in the project cost estimation.
Interestingly, I also found the above query in StackOverFlow. Well, I guess even the best of us needs some help from the internet. Including our DBAs!