Count number of rows and sum up a field of all tables in all relevant schemas with a readonly connection

Question

We have a rather complex database setup which involves 500 schemas. Most of these schemas contain a certain table. This table contains a certain number of rows and those rows contain a size field.

I need to iterate the schemas, detect those schemas that contain the relevant table and count the number of rows and sum up the size field.

The correct SQL statement for one schema looks like this:

SELECT
    TRUNC(SUM((ceil(SMART_REPOSITORY_LOGICAL_FILE.SRLF_SIZE / 4096)*4096)) / (1024*1024),0) AS
    "Volume in MB",
    COUNT(*) AS "number of files"
FROM
    SCHEMA_XYZ.SMART_REPOSITORY_LOGICAL_FILE
WHERE
    SMART_REPOSITORY_LOGICAL_FILE.SRLF_IS_DIRECTORY = 'N'

How could I run this over all schemas that contain the table SMART_REPOSITORY_LOGICAL_FILE ?

I already tried answers available here that involve some XML magic but obviously I don't have the permission to use those calls because I am rejected. I also can not use approaches that involve writing to the schemas.

Answer 1

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 <= 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.