Using dbms xmlgen as a means of dynamic sql

 select table_name
 ,      to_number( extractvalue( xmltype( dbms_xmlgen.getxml( 'select count(*) c from '||table_name) ),'/ROWSET/ROW/C') ) count
 from   user_tables;

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

A more complex example

 SQL> create table mpi_memgc(x number, y varchar2(10), z varchar2(10))
 Table created.
 SQL> insert into mpi_memgc values (1,'bla','bli     ')
 1 row created.
 SQL> commit
 Commit complete.

 SQL> select
   'select count(*) c from "'||table_name||'" where "'||column_name||
      '" like //% // or "'||column_name||
      '" like // %** query,
  table_name,column_name,
  to_number(extractvalue(xmltype(dbms_xmlgen.getxml(
    'select count(*) c from "'||table_name||'" where "'||column_name||
      '" like //% // or "'||column_name||
      '" like // %**)),'/ROWSET/ROW/C')) count
 from user_tab_columns
 where table_name like 'MPI_MEMGC%'
 and table_name not like '%_H'
 and data_type = 'VARCHAR2'
 order by 1;

 QUERY
 --------------------------------------------------------------------------------
 TABLE_NAME                     COLUMN_NAME                         COUNT
 ------------------------------ ------------------------------ ----------
 select count(*) c from "MPI_MEMGC" where "Y" like '% ' or "Y" like ' %'
 MPI_MEMGC                      Y                                       0

 select count(*) c from "MPI_MEMGC" where "Z" like '% ' or "Z" like ' %'
 MPI_MEMGC                      Z                                       1