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:

    TRUNC(SUM((ceil(SMART_REPOSITORY_LOGICAL_FILE.SRLF_SIZE / 4096)*4096)) / (1024*1024),0) AS
    "Volume in MB",
    COUNT(*) AS "number of files"

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.


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:

    v_count integer;
    v_sum   integer;
    for t in (select table_name, owner from all_tables
              where table_name = 'AUDIT_ACTIONS') 
          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
:-------------------------- | :------------ | --------: | --------:
SYS                         | AUDIT_ACTIONS |       180 |     18451

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

  • Doesn't this only use the current schema ? I need to scan all schemas. Besides that I am not allowed to write to the database, so CREATE is forbidden to me. But thanks for the suggestion. – Marged Jun 29 '17 at 4:00
  • Now I understood that my interpretation of all_tables was wrong and that it contains all tables of all schemas. I modified the example above and did a simple EXECUTE IMMEDIATE 'select ... and left out the INSERT INTO part because as I mentioned I am not allowed to write anywhere. Is it possible to run the for and output the SELECT results to the screen in a UNION way ? – Marged Jun 29 '17 at 10:15
  • create the results table as a global temporary table; or are you saying this isn't doable either? as for the UNION question, while you could use the for loop to build up a large multi-UNION select (in a variable), I'm not sure about the limits on how large the variable could get (we're talking about upwards of 500 select statements UNIONed together, right?) – markp Jun 29 '17 at 13:32
  • 505 at present, to be exact ;-) The server running the database must not be altered in any way, you should consider the connection "read only" – Marged Jun 29 '17 at 15:58
  • so convert results to a record/table variable; though how you send the contents back to your client will depend on what you're using as your client (and with that we're pushing the limits of my PL/SQL knowledge without me doing a lot of googling) – markp Jun 29 '17 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.