Count number of rows and sum up a field of all tables in all relevant schemas with a readonly connection
Did you miss Tony Andrew's solution @ answer ?

Here's a dbfiddle example based on Tony's solution: find count and sum from same table w/ different schemas

For the sake of this example I'll setup a second AUDIT_ACTIONS table:

create table AUDIT_ACTIONS as (select * from SYS.AUDIT_ACTIONS where ACTION &lt;= 50)


Create a table to store our results in:

create table results(owner varchar(100), table_name varchar(100), row_count integer, audit_sum integer);


Some minor tweaks to Tony's solution:

declare
    v_count integer;
    v_sum   integer;
begin
    for t in (select table_name, owner from all_tables
              where table_name = 'AUDIT_ACTIONS') 
    loop
          EXECUTE IMMEDIATE 'select count(*), sum(ACTION) from ' || t.owner || '.' || t.table_name
              into v_count, v_sum;
          insert into results (owner, table_name, row_count, audit_sum)
          values (t.owner, t.table_name, v_count, v_sum) ;
    end loop;        
end ;
/


And the results:

select * from results;




OWNER                       | TABLE_NAME    | ROW_COUNT | AUDIT_SUM
:-------------------------- | :------------ | --------: | --------:
FIDDLE_QPLKSTJBEESOQLZCONZA | AUDIT_ACTIONS |        51 |      1275
SYS                         | AUDIT_ACTIONS |       180 |     18451



NOTE: The non-SYS OWNER(SCHEMA) will change with each dbfiddle run.