Andrew Fraser DBA

PL/SQL to count from all tables in a schema

Replace SYSTEM with the name of the schema you are interested in:

set serverout on size 999999
declare
  cnt number ;
begin
  for c1 in (select owner, table_name from all_tables where owner = 'SYSTEM')
  loop
    execute immediate 'select count(1) from '||c1.owner||'.'||c1.table_name into cnt ;
    dbms_output.put_line(c1.table_name||','||cnt) ;
  end loop ;
end ;
/