Get number of records in all tables

if you're content with a possibly estimated count as of the last time statistics were gathered, you can do something like this:

SELECT    table_name
,         num_rows
FROM      all_tables
WHERE     owner  = 'SCOTT'
;

If you want the exact number at run-time, then you need dynamic SQL.  Here's one way to do that:

SELECT    table_name
,         EXTRACTVALUE ( XMLTYPE ( dbms_xmlgen.getxml (  'SELECT  COUNT (*) AS num '
                                                      || 'FROM ' || owner || '.' || table_name
                                                      )
                                 )
                       , '/ROWSET/ROW/NUM'
                       )     AS num_rows
FROM      all_tables
WHERE     owner  = 'SCOTT'
;

[   an XML document can look like this:

<ROWSET>
  <ROW num="1">
    <X>Y</X>
  </ROW>
</ROWSET>

or it can look like this:

<ROWSET><ROW num="1"><X>Y</X></ROW></ROWSET> ]

Either way, you get results like:

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
DEPT                                    4
EMP                                    14
BONUS                                   0
SALGRADE                                5

select  table_name,
        xmlcast(xmlquery(('count(ora:view("' || owner || '","' || table_name || '"))') returning content) as int) cnt
  from  dba_tables
  where owner = 'HR'
  order by table_name
/

TABLE_NAME                            CNT
------------------------------ ----------
COUNTRIES                              25
DEPARTMENTS                            27
EMPLOYEES                             107
JOBS                                   19
JOB_HISTORY                            10
LOCATIONS                              23
REGIONS                                 4
7 rows selected.