Query below lists:

(A) all tables in a database accessible to the current user in Oracle database with their number of rows

(B) all tables in Oracle database with their number of rows

Query was executed under the Oracle12c Database version.

Query

A. All tables accessible to the current user

select owner as schema_name,
       table_name,
       num_rows
from sys.all_tables
where num_rows > 1 
      and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS')
order by num_rows desc;

B. If you have privilege on dba_tables

select owner as schema_name,
       table_name,
       num_rows
from sys.dba_tables
where num_rows > 1
      and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS')
order by num_rows desc;

Columns

• schema_name - schema name
• table name - table name
• num_rows - number of rows in a table

Rows

• One row represents one table
• Scope of rows: all tables in a database containing at least one row
• Ordered by number of rows descending, from largest to smallest (in terms of number of rows)

Sample results