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