Number of Tables by The Number of Rows in Oracle Database

Here I am giving an example to find out number of tables by the number of rows in oracle database.

Query 1: Number of all tables accessible to the current user in Oracle database by the number of rows grouped into predefined intervals.

SELECT row_interval,

       count(*) AS no_of_tables

  FROM (SELECT owner,

               table_name,

               num_rows,

               CASE

                 WHEN num_rows > 1000000000

                 THEN '1b rows and more'

                 WHEN num_rows > 1000000

                 THEN '1m - 1b rows'

                 WHEN num_rows > 1000

                 THEN '1k - 1m rows'

                 WHEN num_rows > 100

                 THEN '100 - 1k rows'

                 WHEN num_rows > 10

                 THEN '10 - 100 rows'

                 ELSE '0 - 10 rows'

               END AS row_interval

          FROM sys.all_tables

-- excluding some Oracle maintained schemas

         WHERE 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 row_interval

 ORDER BY row_interval;

Query 2: Number of all tables in Oracle database by the number of  rows grouped into predefined intervals. (If you have privilege on dba_tables)

SELECT row_interval,

       count(*) AS no_of_tables

  FROM (SELECT owner,

               table_name,

               num_rows,

               CASE

                 WHEN num_rows > 1000000000

                 THEN '1b rows and more'

                 WHEN num_rows > 1000000

                 THEN '1m - 1b rows'

                 WHEN num_rows > 1000

                 THEN '1k - 1m rows'

                 WHEN num_rows > 100

                 THEN '100 - 1k rows'

                 WHEN num_rows > 10

                 THEN '10 - 100 rows'

                 ELSE '0 - 10 rows'

               END AS row_interval

          FROM sys.dba_tables

-- excluding some Oracle maintained schemas

         WHERE 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 row_interval

 ORDER BY row_interval;

Output:

Reference:

• ALL_TABLES
• DATAEDO

That's it.

Happy APEXing!!!...