PL/SQL Example: Count Table Rows in a Schema

 
DECLARE
  cursor c1 IS
    SELECT tname FROM tab;
  tbl varchar2(64 CHAR);
  cnt NUMBER;
BEGIN
  dbms_output.put_line('Start... ');
  OPEN c1;
  loop
    BEGIN
      fetch c1 INTO tbl;
      exit WHEN c1%NOTFOUND;
      EXECUTE IMMEDIATE 'select count(*) c from ' || tbl
        INTO cnt;
      dbms_output.put_line('Table: ' || tbl || ' row count: ' || cnt);
    exception
      WHEN others THEN
      NULL;
    END;
  END loop;
  close c1;
END;
/