Number of Tables by their Size in Oracle Database

SELECT size_interval,        count(*) AS no_of_tables   FROM (SELECT DATA.segment_name table_name,               CASE                WHEN DATA.bytes > 1000000000                THEN ‘1b Bytes and more’                WHEN DATA.bytes > 1000000                THEN ‘1m – 1b Bytes’                WHEN DATA.bytes > 1000                THEN ‘1k – 1m Bytes’                WHEN DATA.bytes > 100                THEN ‘100 – 1k Bytes’                WHEN DATA.bytes > 10                THEN ’10 – 100 Bytes’                ELSE ‘0 – 10 Bytes’               END AS size_interval         FROM (SELECT us.segment_name,                      sum(us.bytes) bytes                 FROM sys.user_segments us,                      sys.all_tables at                WHERE at.table_name = us.segment_name                  AND us.segment_type = ‘TABLE’                   — excluding some Oracle maintained schemas                   AND AT.owner NOT IN (‘ANONYMOUS’,‘CTXSYS’,                                       ‘DBSNMP’,‘EXFSYS’,                                        ‘LBACSYS’, ‘MDSYS’,                                        ‘MGMT_VIEW’,‘OLAPSYS’,                                        ‘OWBSYS’,‘ORDPLUGINS’,                                        ‘ORDSYS’, ‘SI_INFORMTN_SCHEMA’,                                        ‘SYS’,‘SYSMAN’,                                       ‘SYSTEM’, ‘TSMSYS’,                                       ‘DIP’, ‘WKPROXY’,                                       ‘WMSYS’,‘XDB’,                                       ‘APEX_040000’, ‘APEX_PUBLIC_USER’,                                        ‘FLOWS_30000’,‘FLOWS_FILES’,                                        ‘MDDATA’, ‘ORACLE_OCM’,                                        ‘XS$NULL’, ‘SPATIAL_CSW_ADMIN_USR’,                                        ‘SPATIAL_WFS_ADMIN_USR’, ‘PUBLIC’,                                        ‘WK_TEST’,‘WKSYS’, ‘OUTLN’)                GROUP BY us.segment_name              ) DATA)        GROUP BY size_interval        ORDER BY size_interval;