create table test_dm_count(owner varchar2(100),table_name varchar2(100),table_count varchar2(100));
create or replace procedure count_all_tables()
as
sql_count varchar2(100);
sql_insert varchar2(100);
result_count varchar2(100);
sql_truncate varchar2(100);
begin
dbms_output.enable;
sql_truncate:=‘truncate table “SYSDBA”.“TEST_DM_COUNT”’;
execute immediate sql_truncate;
for i in (select owner,table_name from dba_tables where owner not in(‘SYS’,‘CTISYS’,‘SYSAUDITOR’,‘SYSSSO’)) loop
if i.table_name not in(‘##PLAN_TABLE’,‘##HISTOGRAMS_TABLE’,‘TEST_DM_COUNT’) then
sql_count:=‘select count(*) from ‘||i.owner||’.’||i.table_name;
–dbms_output.put_line(sql_count);
execute immediate sql_count INTO result_count;
–dbms_output.put_line(result_count);
sql_insert:=‘insert into test_dm_count values(’‘’||i.owner||‘’‘,’‘’||i.table_name||‘’‘,’‘’||result_count||‘’‘);’;
dbms_output.put_line(sql_insert);
execute immediate sql_insert;
end if;
end loop;
commit;
end;
/

call “SYSDBA”.“COUNT_ALL_TABLES”();
select * from “SYSDBA”.“TEST_DM_COUNT”;