Evaluate Consulting Services

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;

Leave a Comment

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