Script for getting Oracle table size:

Script for getting Oracle table size:

There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function.

There will be a prompt, enter USERNAME to view tables of a particular user.


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;



With Tablespace information:

select sysdate,
       owner,
       segment_name,
       segment_type,
       tablespace_name,
       sum(bytes)/1024/1024/1024 GB
  from dba_segments
 group by owner, segment_type, segment_name, tablespace_name
 order by GB desc;
--------------------------------------------------------------------
Happy to Help !!!